|
|
与 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 users' schemas
解决方案：
GRANT exp_full_database TO scott;
-- or:
GRANT dba TO expdp_role;
错误 6：database link 相关
如果某个特权用户（如 system）利用 NETWORK_LINK 参数导出或导入数据，而使用的以非特权用户（如 scott）
连接远程数据库的 publick database link，则 Import DataPump 或 Export DataPump 作业将执行失败：
> 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
解决方案：
1.确保 database link 用于联句诶远程数据库的用户具有 DBA 角色或 EXP_FULL_DATABASE 角色，或者
授予该用户 expdp_role 角色：
-- in remote database:
GRANT exp_full_database TO scott;
-- or in remote database:
GRANT dba TO expdp_role;
2.如果运行该祖业的特权用户自己创建了以其他特权用户身份连接到远程数据库的的 private database link，则：
-- 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
错误7：trace 文件相关
如果非特权用户使用 TRACE 参数导出或导入数据，则会报如下错误：
> 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
解决方案：
GRANT exp_full_database TO scott;
-- or:
GRANT dba TO expdp_role;
错误8：flashback query 相关
如果具有 EXP_FULL_DATABASE 角色的用户导出其他 schema 下的表并且该表 含有SYS.XMLTYPE 列，
则 Export DataPump 将报如下错误：
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
...
解决方案：
授予运行 Export DataPump job 的用户 FLASHBACK 权限：
GRANT flashback ON hugo.myxmltab TO scott;
-- or:
GRANT flashback any table TO expdp_role;
或者直接授予 DBA 权限
错误9：变化数据捕获 (Change Data Capture, CDC) 相关
如果具有 EXP_FULL_DATABASE 角色的用户只需了全库导出，且该数据库包含变化表(SELECT * FROM change_tables),
则将报如下错误：
...
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
...
参考: Bug:6078613 "EXPORT DATAPUMP AFTER INSTALLING CDC FAILS WITH ORA-942"
解决方案：
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;
或
grant DBA to scott;
错误10：导出的 schema 权限相关
如果被导出的 schema （非启动 export DataPump 作业的用户）有需要导出的计划作业，
且该用户没有足够的表空间配额，则 Export DataPump 作业将报如下错误：
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
解决方案：
为要导出的 schema 的默认表空间授予表空间配额，或者将该 schema 的默认表空间修改为
该 schema 具有足够配额的表空间：
ALTER USER scott QUOTA unlimited ON users;
-- or:
ALTER USER scott DEFAULT TABLESPACE scott_tbsp;
参考：
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
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2013-3-3 21:31:58
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡