执行impdp时出现ORA-39154错误的解决案例
- WBOY原創
- 2016-06-07 16:08:122101瀏覽
一次数据表的导入导出操作在使用impdp导入的时候遇到了ORA-39154，花了点时间解决了。下面我在测试环境里真实还原了这个错误，并
一次数据表的导入导出操作在使用impdp导入的时候遇到了ORA-39154，花了点时间解决了。
下面我在测试环境里真实还原了这个错误，并附上解决思路和方案
#####创建测试表，不过表上的索引建在另一个schema下
sqlplus ad/123456
create table adtab1 tablespace ts_pub as select * from all_users;
SQL> select count(*) from adtab1;
COUNT(*)
----------
45
sqlplus mng/789012
create index ind_adtab1_uid on ad.adtab1(user_id);
create index ind_adtab1_crt on ad.adtab1(created);
#####在expdp所连的源库及impdp所连的目标库上都要创建好Directory对象，并且赋予执行用户ad对于directory的读写权限
sqlplus '/as sysdba'
create or replace directory tmpdir as '/home/Oracle/chh/';
grant read,write on directory tmpdir to ad;
#####以sysdba身份将表从源库导出
expdp \"/ as sysdba\" tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
---导出过程正常
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "AD"."ADTAB1" 6.781 KB 45 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/chh/ad.adtab1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:17:27
#####将dmp文件传输到目标库后以ad用户执行impdp
REVOKE IMP_FULL_DATABASE FROM AD;
impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
---导入过程中出现ORA-39154错误，提示导入的内容里包含有不属于AD用户的对象，这部分对象没有能够正常导入，但ad.adtab1表已经导入成功了
ORA-39154: Objects from foreign schemas have been removed from import
Master table "AD"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "AD"."SYS_IMPORT_FULL_01": ad/******** directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AD"."ADTAB1" 6.781 KB 45 rows
Job "AD"."SYS_IMPORT_FULL_01" successfully completed at 08:20:11
出错原因分析：
因为导入的内容里包括了统计信息，统计信息的相关操作在导入的过程中是在sys.impdp_stats表里进行的(从后面impdp生成的sql脚本里可以看出来)，ad用户需要赋予imp_full_database权限才能导入这部分统计信息，这应该就是ORA-39154的成因
---索引没有导入进去
SQL> select count(*) from adtab1;
COUNT(*)
----------
45
SQL> select index_name,table_name from dba_indexes where table_name='ADTAB1';
no rows selected
#####赋给ad用户imp_full_database权限后再次进行impdp，这回ORA-39083取代了ORA-39154，问题出在为MNG.IND_ADTAB1_UID、MNG.IND_ADTAB1_CRT两个索引生成统计信息时发现这两个索引并不存在，至此我们才发现了索引和表不在同一个schema的问题：表在ad用户下，而索引却建在了mng用户下，这可能是开发人员的一个失误，我们暂且不讨论这样建索引是否合理。
grant imp_full_database to ad;
impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
Master table "AD"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "AD"."SYS_IMPORT_FULL_01": ad/******** directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AD"."ADTAB1" 6.781 KB 45 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG"."IND_ADTAB1_UID" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG"."IND_ADTAB1_CRT" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:
Job "AD"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 08:43:01
---目标库检查确实只有表导入了进来
SQL> select count(*) from adtab1;
COUNT(*)
----------
45
SQL> select index_name,table_name from dba_indexes where table_name='ADTAB1';
no rows selected