執行impdp時出現ORA-39154錯誤的解決案例

最後更新：2015-02-17 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

一次資料表的匯入匯出操作在使用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時是不會收到任何報錯的。這樣造成的結果卻很嚴重：目標庫上的表應該有索引的欄位卻沒有建索引。

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More