执行impdp时出现ORA-39154错误的解决案例
执行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
#####是否因为ad用户是一个普通用户没有权限在mng用户下建索引？于是用sysdba身份再次执行impdp，报错依旧
impdp \"/ as sysdba\" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" 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 "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 08:50:27
SQL> select count(*) from adtab1;
COUNT(*)
----------
45
SQL> select index_name,table_name from dba_indexes where table_name='ADTAB1';
no rows selected
使用sysdba用户导入时也报相同的错误，应该不是由于权限问题引起的，报错信息出现在导入索引统计信息的阶段，因为MNG用户下的两个索引不存在导致了ORA-39083，难道是导出的dmp文件里压根就没有包含这两个索引的信息？
#####将impdp内容重定向到脚本文件，发现脚本里确实没有这两个索引的DDL语句
impdp \"/ as sysdba\" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log sqlfile=ad.adtab1.sql
---ad.adtab1.sql内容，发现除了建表和导入索引的统计信息外，没有create index的步骤
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "AD"."ADTAB1"
( "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"USER_ID" NUMBER NOT NULL ENABLE,
"CREATED" DATE NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_PUB" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
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,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'IND_ADTAB1_UID';
i_o := 'MNG';
EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,45,1,45,1,1,1,0,45,NV,NV,TO_DATE('2015-01-24 08:16:45',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
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,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'IND_ADTAB1_CRT';
i_o := 'MNG';
EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,45,1,39,1,1,1,0,45,NV,NV,TO_DATE('2015-01-24 08:16:45',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
现在方向就很明确了：expdp阶段如何把mng用户下的索引也带上
解决方案如下：
######把表和索引的名称填上，Schemas必须带上ad,mng这两个用户，如果Schema不指定那么默认在SYS下去找，include的功能还是很强大的
expdp \"/ as sysdba\" schemas=ad,mng include=TABLE:\"=\'ADTAB1\'\",INDEX:\"IN \(\'IND_ADTAB1_UID\',\'IND_ADTAB1_CRT\'\)\" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
---导出过程正常
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" schemas=ad,mng include=TABLE:"='ADTAB1'",INDEX:"IN ('IND_ADTAB1_UID','IND_ADTAB1_CRT')" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "AD"."ADTAB1" 6.781 KB 45 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/chh/ad.adtab1.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:22:14
注：上述导出操作如果由普通用户操作，那么必须赋予该用户exp_full_database权限，否则会触发如下错误
ORA-39165: Schema MNG was not found.
ORA-39168: Object path INDEX was not found.
#####在导入目标库之前先将impdp的结果输出到脚本文件
impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log sqlfile=ad.adtab1.sql
---ad.adtab1.sql文件内容中已经包含了create index的步骤
。。。其它部分略
-- CONNECT MNG
CREATE INDEX "MNG"."IND_ADTAB1_UID" ON "AD"."ADTAB1" ("USER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_PUB" PARALLEL 1 ;
ALTER INDEX "MNG"."IND_ADTAB1_UID" NOPARALLEL;
CREATE INDEX "MNG"."IND_ADTAB1_CRT" ON "AD"."ADTAB1" ("CREATED")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_PUB" PARALLEL 1 ;
ALTER INDEX "MNG"."IND_ADTAB1_CRT" NOPARALLEL;
。。。其它部分略
#####最后执行导入
---导入前赋予imp_full_database较色给ad，因为要导入的内容里包含了其它Schema信息
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 SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "AD"."ADTAB1" 6.781 KB 45 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "AD"."SYS_IMPORT_FULL_01" successfully completed at 09:35:56
####结果检查OK
set linesize 180
SQL> select count(*) from adtab1;
COUNT(*)
----------
45
select owner,index_name,status,table_owner,table_name from dba_indexes where table_name='ADTAB1';
OWNER INDEX_NAME STATUS TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ -------- ------------------------------ ------------------------------
MNG IND_ADTAB1_CRT VALID AD ADTAB1
MNG IND_ADTAB1_UID VALID AD ADTAB1
总结：本例中导致ORA-39154的根本问题在于ad表上的索引没有建在ad用户下，这样的情况称为cross schema references，即不同schema的对象间存在关联，cross schema references导致的impdp错误还是比较隐蔽的，好在我们使用了sysdba权限从源库expdp导出表，然后通过impdp时有关统计信息无法导入的ORA-39154错误，一步一步追溯直至发现索引和表不在同一个schema下，问题才得以精确定位。这个案例也告诉我们在table mode export的方式下，如果依赖于a.taba表的对象，比如基于a.taba的索引名为index_b，建在了b用户下，那么下面的命令在导出结果里不会包含b用户下的索引
expdp user/passwd tables=a.taba directory=dirname dumpfile=a.taba.dmp logfile=exp_a.taba.dmp
必须使用schemas、include参数精确指定表名和索引名称，例如：
expdp user/passwd schemas=a,b include=TABLE:\"=\'TABA\'\",INDEX:\"IN \(\'INDEX_B\'\)\" directory=dirname dumpfile=a.taba.dmp logfile=exp_a.taba.dmp
顺便提一句，如果我们一开始从源库导出表的时候没有像下面这样使用sysdba权限
expdp \"/ as sysdba\" tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
而是像下面这样使用ad用户
expdp ad/123456 tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
那么在之后impdp时是不会收到任何报错的。这样造成的结果却很严重：目标库上的表应该有索引的字段却没有建索引。
评论暂时关闭