Tuesday, January 13, 2009

Expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-01031

Error Description
Whenever I run the expdp command to export only my schema objects it fails with ORA-31626, ORA-31633, ORA-06512 and ORA-01031.
A screenshot is below from my console.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp

Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 14:58:53

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges

Cause of the Problem

As ORA-01031 indicates the test user that is performing data pump export does not have enough privilege.

In order to see the current privilege assigned to the user issue,
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
BACKUP ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE
CREATE ANY DIRECTORY
EXECUTE ANY TYPE
ADMINISTER RESOURCE MANAGER
RESUMABLE
SELECT ANY DICTIONARY
READ ANY FILE GROUP

11 rows selected.


Solution of the Problem
In order to do export operation at a minimum level the user must have the create table system privilege. But with above output we see the user does not have "create table" privilege. Do in order to avoid problem we can have different solution.

Solution 1)As a DBA user grant create table privilege to the user who will perform data pump export operation.

SQL> conn system/a
SQL> grant create table to test;


Then as a test user perform your operation.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp

Solution 2)Run the export operation as a different user who has the create table privilege. For example run the export operation as a system user.
Like,
SQL>host expdp system/a full=y directory=d dumpfile=a.dmp

No comments:

Post a Comment