執行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時是不會收到任何報錯的。這樣造成的結果卻很嚴重：目標庫上的表應該有索引的欄位卻沒有建索引。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1412988/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- exp/imp出現錯誤通過expdp/impdp來解決
- steam確認錯誤執行此操作時出現問題怎麼解決
- Win7 IIS7.5執行ASP時出現500錯誤的解決辦法Win7
- oracle rman備份時出現ORA-19502錯誤解決案例Oracle
- ant構建時出現錯誤解決方案
- 卡巴斯機 任務執行時出現錯誤
- 執行Bex 出現 61704 錯誤
- 【故障處理】如何避免在執行impdp後出現ORA-00001錯誤
- 工程make時出現"時鐘錯誤的問題"的解決方案
- expdp/impdp來解決exp/imp出現的錯誤並匯入指定表空間
- 執行jdonMVC示例出現了錯誤MVC
- impdp 遇到 ORA-07445 錯誤。重新EXPDP，IMPDP解決了。
- scp出現錯誤的解決辦法
- 編譯EJB出現錯誤的解決編譯
- nginx出現403錯誤的解決方法Nginx
- Win10系統瀏覽器提示“出現了執行時間錯誤”的解決方法步驟!Win10瀏覽器
- 執行無法解決的編譯錯誤編譯
- Log4J執行時出現的java.lang.VerifyError錯誤JavaError
- jdon執行一段時間後經常出現的錯誤
- impdp時parallel=4導致的錯誤Parallel
- Excel宏執行時提示錯誤1004的三個解決方法Excel
- 轉存——Quartus II FPGA程式模擬執行時出現錯誤“error occurred during modelsim simulation”的解決方法FPGAError
- JDBC執行出現ORA-17410錯誤JDBC
- 通過dbms_scheduler執行一個外部job時出現錯誤
- ontape出現段錯誤（未解決篇）
- jdon框架在jboss中執行有時時出現出現錯誤，是什麼原因，如何處理框架
- 在 11.2.0.4 執行 utlrp.sql 出現 ORA-04031 錯誤解決方法SQL
- 在使用jbuild7執行jive論壇的時候出現下列錯誤UI
- 解決呼叫DBMS_OUTPUT時出現緩衝器溢位的錯誤
- 用CMD執行時Java，出現亂碼的解決辦法Java
- 使用rman執行recover database 的時候出現RMAN-06054的錯誤提示Database
- 解決aix下不能執行netca的錯誤AI
- 解決POI多執行緒匯出時資料錯亂問題執行緒
- mysql執行函式出現1418錯誤MySql函式
- exchange出現1025錯誤！（未解決）
- Mysql出現連線錯誤解決辦法MySql
- Oracle資料庫多條sql執行語句出現錯誤時的控制方式Oracle資料庫SQL
- 執行JBoss 5.1.0 GA時出現Error installing to Instantiated:name=AttachmentStore state=Described錯誤的解決辦法Error