Oracle Data Pump 工具系列：Data Pump 权限配置相关异常及解决方法汇总
日期：2014-05-16 浏览次数：20508 次
Oracle Data Pump 工具系列：Data Pump 权限配置相关错误及解决办法汇总
与 Data Pump 权限相关的错误及解决办法：
示例语句：
> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=exp_s.dmp \
LOGFILE=exp_s.log SCHEMAS=scott
错误1：
UDE-00008: operation generated ORACLE error 1045
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied
解决方案： 授予运行该 export 作业的用户 CREATE SESSION 权限，或者授予运行该 export job 的用户所授予的 expdp_role 角色 CREATE SESSION 权限：
GRANT create session TO scott;
-- or:
GRANT create session TO expdp_role;
错误2：Master table 相关
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
解决方案：
GRANT create table TO scott;
-- or:
GRANT create table TO expdp_role;
错误3：Directory 相关
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
解决方案：
GRANT read, write ON DIRECTORY my_dir TO scott;
-- or:
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
注意：如果在授予上述权限后错误重现，则磁盘上的目录可能是在 directory 对象创建后创建的，
这时需要先在数据库中 drop 该 directory 对象，确保 Oracle 数据库所安装的服务器文件系统上已存在该目录
然后再数据库中重建该 directory 对象，然后再按照上述方法授予读写权限。
错误4：表空间配额相关
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-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 "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'
解决方案：
ALTER USER scott QUOTA unlimited ON users;
-- or:
ALTER USER scott DEFAULT TABLESPACE scott_tbsp;
错误5：特权用户相关
> 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
与 Data Pump 权限相关的错误及解决办法：
示例语句：
> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=exp_s.dmp \
LOGFILE=exp_s.log SCHEMAS=scott
错误1：
UDE-00008: operation generated ORACLE error 1045
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied
解决方案： 授予运行该 export 作业的用户 CREATE SESSION 权限，或者授予运行该 export job 的用户所授予的 expdp_role 角色 CREATE SESSION 权限：
GRANT create session TO scott;
-- or:
GRANT create session TO expdp_role;
错误2：Master table 相关
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
解决方案：
GRANT create table TO scott;
-- or:
GRANT create table TO expdp_role;
错误3：Directory 相关
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
解决方案：
GRANT read, write ON DIRECTORY my_dir TO scott;
-- or:
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
注意：如果在授予上述权限后错误重现，则磁盘上的目录可能是在 directory 对象创建后创建的，
这时需要先在数据库中 drop 该 directory 对象，确保 Oracle 数据库所安装的服务器文件系统上已存在该目录
然后再数据库中重建该 directory 对象，然后再按照上述方法授予读写权限。
错误4：表空间配额相关
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-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 "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'
解决方案：
ALTER USER scott QUOTA unlimited ON users;
-- or:
ALTER USER scott DEFAULT TABLESPACE scott_tbsp;
错误5：特权用户相关
> 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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
