Minimum Requirements to Use Export DataPump and IMP(System Privileges)_351598.1

rongshiyuan發表於2014-05-13

Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) (Doc ID 351598.1)

In this Document

Goal

Solution

1. Minimum Requirements.

2. How to grant privileges.

3. Privileged User.

4. Useful queries.

5. Possible errors.

Additional Resources

References

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 7-Feb-2011***

Goal

In Oracle10g, the new database export and import utilities Export DataPump (expdp) and Import DataPump (impdp) were introduced. This document describes the minimum requirements to run an Export DataPump or Import DataPump job.

Solution

1. Minimum Requirements.

The minimum requirements to run an Export DataPump or Import DataPump job on an Oracle10g or any higher release database are:

- system privilege CREATE SESSION (or the old role: CONNECT)
- system privilege CREATE TABLE
- object privileges READ and WRITE on an valid directory object (or the CREATE DIRECTORY privilege with which a valid directory object was created)
- sufficient tablespace quota on the user's default tablespace (needed to create the so-called Master Table that is used by the DataPump job).

In addition, the role EXP_FULL_DATABASE is needed:
- to run a full database Export DataPump job or
- to run a transport_tablespace Export DataPump job or
- to run an Export DataPump job with the TRACE parameter or
- to run an Export DataPump job that exports a different schema.

In addition, the role IMP_FULL_DATABASE is needed:
- to run a full database Import DataPump job or
- to run a transport_datafiles Import DataPump job or
- to run an Import DataPump job with the TRACE parameter or
- to run an Import DataPump job that imports a different schema.

Note that these requirements apply to the user that connects to the database when running the Export DataPump or Import DataPump job, not to the user that is exported/imported.

2. How to grant privileges.

The privileges can be granted explicitly or via a role. E.g.:

CONNECT system/manager
CREATE DIRECTORY my_dir AS 'full_pre_existing_directory_path_here';
GRANT create session, create table TO scott IDENTIFIED BY tiger;
GRANT read, write ON DIRECTORY my_dir TO scott;
ALTER USER scott DEFAULT TABLESPACE USERS;
ALTER USER scott QUOTA unlimited ON users;

-- or:

CONNECT system/manager
CREATE DIRECTORY my_dir AS 'full_pre_existing_directory_path_here>>';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO scott;
ALTER USER scott DEFAULT ROLE all;
ALTER USER scott DEFAULT TABLESPACE USERS;
ALTER USER scott QUOTA unlimited ON users;

3. Privileged User.

A privileged user is a user that has the EXP_FULL_DATABASE role for an Export DataPump and the IMP_FULL_DATABASE role for an Import DataPump job, or the DBA role that contains both these roles:

SET lines 80
COL privilege FOR a40
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2;

GRANTEE              GRANTED_ROLE                   DEF
-------------------- ------------------------------ ---
DBA                  EXP_FULL_DATABASE              YES
DBA                  IMP_FULL_DATABASE              YES
SCOTT                EXP_FULL_DATABASE              YES
SCOTT                IMP_FULL_DATABASE              YES
SYS                  DBA                            YES
SYS                  EXP_FULL_DATABASE              YES
SYS                  IMP_FULL_DATABASE              YES
SYSMAN               DBA                            YES
SYSTEM               DBA                            YES

4. Useful queries.

-- check for granted system privileges CREATE SESSION and CREATE TABLE
-- (replace SCOTT with the username that connects to the database when
-- running the DataPump job):

SET lines 80 pages 50
COL privilege FOR a40
SELECT grantee, privilege
  FROM dba_sys_privs
WHERE (grantee IN ('SCOTT', 'PUBLIC')
        OR grantee IN (SELECT granted_role FROM dba_role_privs
                        WHERE grantee IN ('SCOTT', 'PUBLIC')))
   AND privilege IN ('CREATE SESSION', 'CREATE TABLE')
ORDER BY 1,2;

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
SCOTT                          CREATE SESSION
SCOTT                          CREATE TABLE

-- check for granted roles and the default role (replace SCOTT with the
-- username that connects to the database when running the DataPump job):

SET lines 80
SELECT grantee, granted_role, default_role
  FROM dba_role_privs
WHERE grantee IN ('SCOTT', 'PUBLIC') ORDER BY 1,2;

GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SCOTT                          EXP_FULL_DATABASE              YES
SCOTT                          IMP_FULL_DATABASE              YES

-- check for granted privileges on directory objects (replace SCOTT with
-- the username that connects to the database when running the DataPump job):

SET lines 100
COL privilege FOR a10
COL grantee FOR a20
COL owner FOR a20
SELECT p.grantee, p.privilege, p.owner, d.directory_name
  FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
   AND (grantee IN ('SCOTT', 'PUBLIC')
        OR grantee IN (SELECT granted_role FROM dba_role_privs
                        WHERE grantee IN ('SCOTT', 'PUBLIC')))
ORDER BY 4,3,2;

GRANTEE              PRIVILEGE  OWNER                DIRECTORY_NAME
-------------------- ---------- -------------------- ----------------------
IMP_FULL_DATABASE    READ       SYS                  DATA_PUMP_DIR
EXP_FULL_DATABASE    READ       SYS                  DATA_PUMP_DIR
EXP_FULL_DATABASE    WRITE      SYS                  DATA_PUMP_DIR
IMP_FULL_DATABASE    WRITE      SYS                  DATA_PUMP_DIR
SCOTT                READ       SYS                  MY_DIR
SCOTT                WRITE      SYS                  MY_DIR

-- check for the default tablespace (replace SCOTT with the username that
-- connects to the database when running the DataPump job):

SET lines 80
SELECT username, default_tablespace
  FROM dba_users WHERE username IN ('SCOTT');

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT                          USERS

-- check for the tablespace quota (replace SCOTT with the username that
-- connects to the database when running the DataPump job):

SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
  FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('SCOTT');

USERNAME             TABLESPACE_NAME             BYTES    MAX_BYTES
-------------------- -------------------- ------------ ------------
SCOTT                USERS                      393216           -1

5. Possible errors.

Possible errors if user SCOTT tries to run an Export DataPump job with:

> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=exp_s.dmp \
LOGFILE=exp_s.log SCHEMAS=scott

5.1. Session related.

UDE-00008: operation generated ORACLE error 1045
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied

Solution: grant the CREATE SESSION privilege to the user that runs the export (scott) or grant it to the role (expdp_role) that is granted to the user that runs the export, e.g.:

GRANT create session TO scott;
-- or:
GRANT create session TO expdp_role;

Alternatively, run the export with a different privileged user that has the 'CREATE SESSION' privilege, or the old 'CONNECT' role (which has the CREATE SESSION privilege), e.g.: run the export while connecting with the SYSTEM schema.

5.2. Master table related.

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 "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges

Solution: grant the CREATE TABLE privilege to the user that runs the export (scott) or grant it to the role (expdp_role) that is granted to the user that runs the export, e.g.:

GRANT create table TO scott;
-- or:
GRANT create table TO expdp_role;

Alternatively, run the export with a different privileged user that has the 'CREATE TABLE' privilege, e.g.: run the export while connecting with the SYSTEM schema.

5.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 the READ and WRITE privilege on the DIRECTORY object to the user that runs the export (scott) or grant it to the role (expdp_role) that is granted to the user that runs the export, e.g.:

GRANT read, write ON DIRECTORY my_dir TO scott;
-- or:
GRANT read, write ON DIRECTORY my_dir TO expdp_role;

Alternatively, run the export with a different privileged user that has the 'READ' and 'WRITE' privilege on the directory object, e.g.: run the export while connecting with the user that created the directory object.
Note: If the errors reproduce after (re-)granting the privileges, then the directory on disk may have been created after the directory object was created. In that case:
- drop the directory object in the database;
- ensure that the directory exists on the filesystem on the server where the Oracle database is installed, and that the Oracle user has access to it;
- re-create the directory object in the database;
- grant the read and write privileges on the directory object to the user(s) who connect to the database when starting the expdp / impdp job.

5.4. Tablespace quota related.

Solution: grant tablespace quota on the default tablespace of the user that runs the export (scott), or change the default tablespace of the user that runs the export to a tablespace on which this user has sufficient quota, e.g.:

ALTER USER scott QUOTA unlimited ON users;
-- or:
ALTER USER scott DEFAULT TABLESPACE scott_tbsp;

Alternatively, run the export with a different privileged user that has sufficient quota on their default tablespace.

5.5. Privileged User related.

> 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, 2005, 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, 2005, 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 the DBA role or the EXP_FULL_DATABASE role to the user that runs the export (scott) or grant it to the role (expdp_role) that is granted to the user that runs the export, e.g.:

GRANT exp_full_database TO scott;
-- or:
GRANT dba TO expdp_role;

Alternatively, run the export with a privileged user (i.e. a user who has the DBA or the EXP_FULL_DATABASE role).

5.6. Database link related.
If a privileged user (e.g. SYSTEM) is exporting or importing data with the NETWORK_LINK parameter and is using a (public) database link that connects to a remote database as a non-privileged user (e.g. SCOTT), then the Import DataPump or Export DataPump job will fail with:

> 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, 2005, 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: ensure that the user in the remote database with which the database link connects to, has the DBA role or the EXP_FULL_DATABASE role, or grant it to the role (expdp_role) that is granted to the user that the database link connects to, e.g.:

-- in remote database:
GRANT exp_full_database TO scott;
-- or in remote database:
GRANT dba TO expdp_role;

Alternatively, have the privileged user that runs the job create a private database link of its own, that connects as another privileged user to the remote database.

-- 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

5.7. Trace file related.
If a non-privileged user is exporting or importing data with the TRACE parameter, then the Export DataPump or Import DataPump job will fail with:

> 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, 2005, 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: when the Data Pump TRACE parameter is used in Oracle10g Release 2 or higher, then the user who connects to the database and runs the export DataPump job needs to have the DBA role or the EXP_FULL_DATABASE role (the same applies to Import DataPump with TRACE parameter: DBA or IMP_FULL_DATABASE role required), e.g.:

GRANT exp_full_database TO scott;
-- or:
GRANT dba TO expdp_role;

Alternatively, run the export with TRACE parameter with a privileged user (i.e. a user who has the DBA or the EXP_FULL_DATABASE role).
Reference:
Bug:5152186 "EXPDP/IMPDP JOB WITH TRACE AND STARTED WITH NON-DBA USER FAILS: ORA-31631"

5.8. Flashback query related.
If a user with the EXP_FULL_DATABASE role is exporting a table that is owned by a different schema and that table has as SYS.XMLTYPE column, then the Export DataPump job will fail with:

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/TABLE
ORA-31693: Table data object "HUGO"."MYXMLTAB" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
...

Solution: explicitly grant the FLASHBACK privilege to the schema that runs the Export DataPump job. The privilege can be granted on specific objects (privilege: FLASHBACK), or on all tables (privilege: FLASHBACK ANY TABLE), e.g.:

GRANT flashback ON hugo.myxmltab TO scott;
-- or:
GRANT flashback any table TO expdp_role;

Alternatively, grant the DBA role to the schema that runs the export DataPump job or run the export with a schema that has the DBA role.

5.9. Change Data Capture (CDC) related.
If a user with the EXP_FULL_DATABASE role performs a full database export, and the database has a change table (SELECT * FROM change_tables), then the Export DataPump job may fail with:

...
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 []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.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 []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CDC_EXPDP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.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"
Workaround: explicitly grant the following privileges to the schema that runs the full database Export DataPump job, e.g.:

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;

Alternatively, grant the DBA role to the schema that runs the export DataPump job or run the export with a schema that has the DBA role.

5.10. Exported schema privileges related.
If the schema that is being exported (i.e. not the user that started the Export DataPump job) has scheduler jobs that need to be exported, and that user does not have (enough) tablespace quota, then the Export DataPump job will fail with:

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,0,1,'10.02.00.03.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 tablespace quota to the default tablespace of the schema that needs to be exported, or change the default tablespace of the schema that needs to be exported to a tablespace on which this schema has sufficient quota, e.g.:

ALTER USER scott QUOTA unlimited ON users;
-- or:
ALTER USER scott DEFAULT TABLESPACE scott_tbsp;

Additional Resources

Community: Database Utilities

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.

References

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