Oracle Data Pump tool series: Data Pump permission configuration errors and Solutions

Errors related to data pump permissions and solutions:

Example Statement:
> Expdp Scott/tiger directory = my_dir dumpfile = exp_s.dmp \
Logfile = exp_s.log schemas = Scott

Error 1:
UDE-00008: operation generated Oracle error 1045
The ORA-01045: User Scott lacks create session privilege; logon denied

Solution: grant the create session permission to the user running the export job, or grant the expdp_role role create session permission to the user running the export job:
 
Grant create session to Scott;
-- Or:
Grant create session to expdp_role;

Error 2: master table Problems

ORA-31626: job does not exist
ORA-31633: Unable to create master table "Scott. sys_export_schema_01"
ORA-06512: At "SYS. dbms_sys_error", line 95
ORA-06512: AT & quot; SYS. KUPV $ FT & quot;, line 863
ORA-01031: insufficient privileges

Solution:
Grant create table to Scott;
-- Or:
Grant create table to expdp_role;

Error 3: Directory related

ORA-39002: Invalid operation.
ORA-39070: Unable to open the log file.
ORA-39087: directory name my_dir is invalid.
 
ORA-39001: invalid argument Value
ORA-39000: Bad Dump File Specification
ORA-39087: directory name my_dir is invalid.
 
ORA-39002: Invalid operation.
ORA-39070: Unable to open the log file.
ORA-31631: privileges are required.
 
ORA-39001: invalid argument Value
ORA-39000: Bad Dump File Specification
ORA-31631: privileges are required.

Solution:
Grant read, write on directory my_dir to Scott;
-- Or:
Grant read, write on directory my_dir to expdp_role;

Note: if an error occurs after the preceding permissions are granted, the directory on the disk may be created after the directory object is created,
In this case, you need to drop the directory object in the database to ensure that the directory already exists on the server file system installed in the Oracle database.
Then re-create the directory object in the database, and then grant the read and write permissions according to the above method.

Error 4: Table space quota

ORA-31626: job does not exist
ORA-31633: Unable to create master table "Scott. sys_export_schema_01"
ORA-06512: At "SYS. dbms_sys_error", line 95
ORA-06512: AT & quot; SYS. KUPV $ FT & quot;, line 863
ORA-01536: space quota exceeded for tablespace 'users'
 
ORA-31626: job does not exist
ORA-31633: Unable to create master table "Scott. sys_export_schema_01"
ORA-06512: At "SYS. dbms_sys_error", line 95
ORA-06512: AT & quot; SYS. KUPV $ FT & quot;, line 863
ORA-01950: no privileges on tablespace 'users'

Solution:
Alter user Scott quota unlimited on users;
-- Or:
Alter user Scott default tablespace scott_tbsp;
 
Error 5: privileged users

> Expdp Scott/tiger directory = my_dir dumpfile = expdp_s.dmp \
Logfile = expdp_s.log transport_tablespaces = users

Export: Release 10.2.0.3.0-production on Monday, 30 July, 2007 10:03:59
Copyright (c) 2003,200 5, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
ORA-31631: privileges are required.
ORA-39162: transportable tablespace job require privileges
 
> Expdp Scott/tiger directory = my_dir dumpfile = expdp_s.dmp \
Logfile = expdp_s.log tables = Hugo. EMP

Export: Release 10.2.0.3.0-production on Monday, 30 July, 2007 11:51:25
Copyright (c) 2003,200 5, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
ORA-31631: privileges are required.
ORA-39109: unprivileged users may not operate upon other users 'schemas

Solution:

Grant exp_full_database to Scott;
-- Or:
Grant DBA to expdp_role;

Error 6: database link problems
If a privileged user (such as system) uses the network_link parameter to export or import data, the non-privileged user (such as Scott)
If you connect to the publick database link of the remote database, the import DataPump or export DataPump job will fail to run:

> Impdp system/manager nologfile = y network_link = scott_pub_dblink \
Schemas = Scott remap_schema = SCOTT: Hugo

Import: Release 10.2.0.3.0-production on Thursday, 23 August, 2007 11:49:30
Copyright (c) 2003,200 5, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
ORA-31631: privileges are required.
ORA-39149: cannot link privileged user to non-privileged user

Solution:
1. Make sure that the database link is used by the remote database user to have the DBA role or the exp_full_database role, or
Grant the user the role expdp_role:
-- In remote database:
Grant exp_full_database to Scott;
-- Or in remote database:
Grant DBA to expdp_role;

2. If the privileged user running the parent business creates a private database link that connects to the remote database as another privileged user, then:
-- In local database:
Connect system/Manager
Create database link system_to_remote
Connect to system identified by manager
Using 'remote _ db.oracle.com ';

> Impdp system/manager nologfile = y network_link = system_to_remote \
Schemas = Scott remap_schema = SCOTT: Hugo

Error 7: trace file related
If a non-authorized user uses the trace parameter to export or import data, the following error is returned:

> Expdp Scott/tiger directory = my_dir dumpfile = expdp_s.dmp \
Logfile = expdp_s.log tables = EMP trace = 480300
 
Export: Release 10.2.0.3.0-production on Monday, 30 July, 2007 12:44:30
Copyright (c) 2003,200 5, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
ORA-31631: privileges are required.

Solution:
Grant exp_full_database to Scott;
-- Or:
Grant DBA to expdp_role;
 
Error 8: flashback query Problems

If a user with the role of exp_full_database exports tables in other Schemas and the table contains the SYS. xmltype column,
Then, the following error is reported:

File: expdp_s.par
-----------------
Directory = my_dir
Dumpfile = expdp_s.dmp
Logfile = expdp_s.log
Flashback_time = "to_timestamp (to_char (sysdate, 'yyyy-MM-DD hh24: MI: ss'), 'yyyy-MM-DD hh24: MI: ss ')"

> Expdp Scott/tiger schemas = Hugo parfile = expdp_s.par

...
Processing object type schema_export/table
ORA-31693: Table Data Object "Hugo". "myxmltab" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
...
 
Solution:
Grant the flashback permission to the user running the export DataPump job:

Grant flashback on Hugo. myxmltab to Scott;
-- Or:
Grant flashback any table to expdp_role;

Or directly grant DBA permissions.

Error 9: Change Data Capture (CDC)

If a user with the exp_full_database role only needs to export the entire database and the database contains a change table (select * From change_tables ),
The following error is reported:
...
Estimate in progress using blocks method...
Processing object type database_export/Schema/table/table_data
ORA-39125: worker unexpected fatal error in KUPW $ worker. get_table_data_objects while calling dbms_metadata.fetch_xml_clob []
The ORA-31642: The following SQL statement fails:
Begin "sys". "dbms_cdc_expdp". schema_callout (:, 1, '10. 02.0000003.00'); end;
ORA-06512: At "SYS. dbms_sys_error", line 86
ORA-06512: At "SYS. DBMS_METADATA", line 907
ORA-01031: insufficient privileges

ORA-06512: At "SYS. dbms_sys_error", line 95
ORA-06512: At "SYS. KUPW $ worker", line 6249
...
 
...
Estimate in progress using blocks method...
Processing object type database_export/Schema/table/table_data
ORA-39125: worker unexpected fatal error in KUPW $ worker. get_table_data_objects while calling dbms_metadata.fetch_xml_clob []
The ORA-31642: The following SQL statement fails:
Begin "sys". "dbms_cdc_expdp". schema_callout (:, 1, '10. 02.0000003.00'); end;
ORA-06512: At "SYS. dbms_sys_error", line 86
ORA-06512: At "SYS. DBMS_METADATA", line 907

ORA-00942: Table or view does not exist

ORA-06512: At "SYS. dbms_sys_error", line 95
ORA-06512: At "SYS. KUPW $ worker", line 6249

...
 
Reference: Bug: 6078613 "Export DataPump after installing CDC fails with ORA-942"

Solution:
Grant create any table to Scott;
Grant insert any table to Scott;
Grant drop any table to Scott;
Grant select on cdc_change_sources $ to Scott;
Grant select on cdc_change_sets $ to Scott;
Grant select on cdc_change_tables $ to Scott;
Grant select on cdc_subscribers $ to Scott;

Or

Grant DBA to Scott;

Error 10: schema permission for export

If the exported Schema (not the user who starts the export DataPump job) has a scheduled job to be exported,
If the user does not have enough tablespace quota, the following error is returned for the export DataPump job:

ORA-39125: worker unexpected fatal error in KUPW $ worker. get_table_data_objects while calling dbms_metadata.fetch_xml_clob []
The ORA-31642: The following SQL statement fails:
Begin "sys". "dbms_sched_export_callouts". schema_callout (:, 1, '10. 02.0000003.00 '); end;
ORA-06512: At "SYS. dbms_sys_error", line 86
ORA-06512: At "SYS. DBMS_METADATA", line 907
ORA-01950: no privileges on tablespace 'users'

ORA-06512: At "SYS. dbms_sys_error", line 95
ORA-06512: At "SYS. KUPW $ worker", line 6249

----- PL/SQL call stack -----
Object line Object
Handle number name
2dfb4638 14938 package body SYS. KUPW $ worker
2dfb4638 6314 package body SYS. KUPW $ worker
2dfb4638 9129 package body SYS. KUPW $ worker
2dfb4638 1882 package body SYS. KUPW $ worker
2dfb4638 6875 package body SYS. KUPW $ worker
2dfb4638 1260 package body SYS. KUPW $ worker
2dc76be4 2 anonymous Block

Job "system". "sys_export_schema_02" stopped due to fatal error at 14:38:18

Solution:

Grant the tablespace quota for the default tablespace of the schema to be exported, or change the default tablespace of the schema
Table space with sufficient quota for this schema:

Alter user Scott quota unlimited on users;
-- Or:
Alter user Scott default tablespace scott_tbsp;

Refer:

BUG: 4540755-about a minimum privilege necessary to execute Data Pump
BUG: 5152186-expdp/impdp job with trace and started with NON-DBA user fails: ORA-31631
BUG: 5169420-import from database via network_link failed with error ORA-39149
BUG: 6078613-export DataPump after installing CDC fails with ORA-942
Note: 266875.1-export/import DataPump parameter directory-how to specify a directory
BUG: 4540755-about a minimum privilege necessary to execute Data Pump

Note: 286496.1-export/import DataPump parameter trace-how to diagnose Oracle Data Pump

For reprint, please indicate the source and original article links:

Http://blog.csdn.net/xiangsir/article/details/8628351