[20170421]impdp匯入問題data_options=SKIP_CONSTRAINT_ERRORS.txt

--//一般年前我們經常要做一些匯入匯出操作,經常會遇到主鍵衝突問題.為此浪費時間,我一般先匯入另外的表名,檢查
--//沒有主鍵衝突之類問題,在插入表中.一些純手工操作,很繁瑣.

--//前幾天看書,發現實際上impdp除了支援table_exists_action=append選項外,還有一個引數避免主鍵衝突時報錯.
--//data_options=SKIP_CONSTRAINT_ERRORS

--//這樣有問題的記錄不用匯入,中途也不會報錯.終止匯入操作.學習測試看看.

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t as select rownum id,lpad('x',10,'x') name from dual connect by level<=10;
create unique index pk_t on t(id);
alter table t add constraint pk_t primary key (id);

$ expdp scott/book DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp tables=t
Export: Release 11.2.0.4.0 - Production on Fri Apr 21 10:22:02 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/a** DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp tables=t
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SCOTT"."T" 5.570 KB 10 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/book/dpdump/t.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Apr 21 10:22:12 2017 elapsed 0 00:00:08

update t set id=id+8;
commit ;

--//這樣存在2條記錄無法匯入的情況id=9,id=10.

2.開始匯入:
$ impdp scott/book DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp table_exists_action=append tables=t

Import: Release 11.2.0.4.0 - Production on Fri Apr 21 10:23:08 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a* DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp table_exists_action=append tables=t
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "SCOTT"."T" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.PK_T) violated
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Fri Apr 21 10:23:13 2017 elapsed 0 00:00:04

SCOTT@book> select count(*) from t;
COUNT(*)
----------
10

--//可以存在主鍵衝突，沒有匯入成功。解決方法就是使用引數data_options=SKIP_CONSTRAINT_ERRORS。

$ impdp scott/book DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp table_exists_action=append tables=t data_options=SKIP_CONSTRAINT_ERRORS
Import: Release 11.2.0.4.0 - Production on Fri Apr 21 10:24:48 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a* DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp table_exists_action=append tables=t data_options=SKIP_CONSTRAINT_ERRORS
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T" 5.570 KB 8 out of 10 rows
2 row(s) were rejected with the following error:
ORA-00001: unique constraint (SCOTT.PK_T) violated

Rejected rows with the primary keys are:
Rejected row #1:
column ID: 9
Rejected row #2:
column ID: 10
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Fri Apr 21 10:24:55 2017 elapsed 0 00:00:07

SCOTT@book> select count(*) from t;
COUNT(*)
----------
18

--//oracle僅僅列出了衝突的鍵值
Rejected row #1:
column ID: 9
Rejected row #2:
column ID: 10

--//如果存在大量衝突，這樣顯示不是很好。是否有好的方法記錄衝突的記錄呢？

3.重複測試：
--//開啟跟蹤：trace=FFF0300
$ impdp scott/book DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp table_exists_action=append tables=t data_options=SKIP_CONSTRAINT_ERRORS trace=FFF0300

--//在跟蹤檔案中dw00

KUPD:10:48:40.724: CREATE TABLE "ET$007B00070001"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(10)
   ) ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "DATA_PUMP_DIR" ACCESS PARAMETERS ( DEBUG = (3 , 268370688) DATAPUMP INTERNAL TABLE "SCOTT"."T" JOB ( "SCOTT","SYS_IMPORT_TABLE_01",1) WORKERID 1 PARALLEL 1 VERSION '11.2.0.4.0' ENCRYPTPASSWORDISNULL COMPRESSION DISABLED EN
CRYPTION DISABLED TABLEEXISTS) LOCATION ('bogus.dat') ) PARALLEL 1 REJECT LIMIT UNLIMITED

--//會建立一個外部表bogus.dat. bogus表示假的；偽造的

KUPD:10:48:40.821: INSERT /*+ PARALLEL("T",1)+*/ INTO RELATIONAL("SCOTT"."T" NOT XMLTYPE) ("ID", "NAME")
SELECT "ID", "NAME"
FROM "ET$007B00070001" KU$ LOG ERRORS INTO "SCOTT"."ERR$DP007B00070001" REJECT LIMIT UNLIMITED

--//然後插入"SCOTT"."ERR$DP007B00070001"表。

$ grep -n -i drop book_dw00_54697.trc
666: stmt := 'DROP TABLE "ET$007B00070001" PURGE';
677: stmt := 'DROP TABLE "ET$007B00070001" PURGE';
706:KUPD:10:48:40.720: Verb item: DROP
833:KUPD:10:48:41.031: Drop external table, ET$007B00070001
834:KUPD:10:48:41.118: Table ET$007B00070001 dropped

--//而且看到在結束時刪除表以及外部表。如何截獲保留這些表呢？想起以前給開發寫禁止刪除的指令碼。

CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
   BEFORE TRUNCATE OR DROP
   ON DATABASE
BEGIN
   IF     ora_dict_obj_type = 'TABLE'
      AND ora_dict_obj_owner LIKE 'SCOTT%'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_JOURNAL\_%' ESCAPE '\'
   THEN
      raise_application_error
      (
         -20000
        ,'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!'
      );
   END IF;
END;
/

ORA-31693: Table data object "SCOTT"."T" failed to load/unload and is being skipped due to error:
ORA-20000: YOU CAN NOT TRUNCATE or DROP ET$005A00030001 TABLE!
ORA-06512: at line 6
ORA-06512: at "SYS.KUPD$DATA", line 1883
ORA-06512: at "SYS.KUPD$DATA", line 3541
ORA-20000: YOU CAN NOT TRUNCATE or DROP ET$005A00030001 TABLE!
ORA-06512: at line 6
ORA-06512: at "SYS.KUPD$DATA", line 1883
ORA-06512: at "SYS.KUPD$DATA", line 2176
ORA-20000: YOU CAN NOT TRUNCATE or DROP ET$005A00030001 TABLE!
--//不行，加入條件 and ORA_DICT_OBJ_NAME NOT LIKE 'ET$%' ，也不行。
--//還有什麼方法呢？google找到如下連結：

--//session 1:
SCOTT@book> lock table t in share mode;
Table(s) Locked.

--//執行:
$ impdp scott/book DIRECTORY=DATA_PUMP_DIR DUMPFILE=t.dp table_exists_action=append tables=t data_options=SKIP_CONSTRAINT_ERRORS

SCOTT@book> column table_name format a30
SCOTT@book> select table_name from user_tables where table_name like 'ERR$DP%';
TABLE_NAME
------------------------------
ERR$DP008600050001

--//開啟另外會話執行,session 2:
SCOTT@book> lock table ERR$DP008600050001 in row share mode;
Table(s) Locked.

--//session 1:
SCOTT@book> rollback ;
Rollback complete.

--//等匯入結束。
--//session 2:
SCOTT@book> rollback ;
Rollback complete.

--//再次查詢ERR$DP008600050001裡面就記錄失敗的匯入記錄，感覺oracle應該有什麼引數保留這些表，而不是結束時drop。
SCOTT@book> SELECT ORA_ERR_MESG$, id, name FROM ERR$DP008600050001;
ORA_ERR_MESG$ ID NAME
--------------------------------------------------- -- ----------
ORA-00001: unique constraint (SCOTT.PK_T) violated 1 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 2 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 3 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 4 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 5 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 6 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 7 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 8 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 9 xxxxxxxxxx
ORA-00001: unique constraint (SCOTT.PK_T) violated 10 xxxxxxxxxx
10 rows selected.

--//因為前面已經匯入，這次自然是全部記錄都存在衝突。
--//從另外方面也可以發現以前指令碼存在問題，比如SYS_IMPORT_TABLE_01表，還有像前面的情況。

4.下午想了一下，想起建立表時加入disable table lock，就可以防止刪除。連結
--// http://blog.itpub.net/267265/viewspace-2103538/

--//建立如下觸發器：
CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_table
after create
   ON DATABASE
BEGIN
   IF     ora_dict_obj_type = 'TABLE'
      AND ora_dict_obj_owner LIKE 'SCOTT%'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_JOURNAL\_%' ESCAPE '\'
      and ORA_DICT_OBJ_NAME LIKE 'ERR$DP%'
   THEN
      EXECUTE IMMEDIATE 'alter table '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' disable table lock ';
   END IF;
END;
/

SCOTT@book> select table_name from user_tables where table_name like 'ERR$DP%';
TABLE_NAME
------------------------------
ERR$DP008600070001

--OK! 現在表不被刪除了。

5.收尾：
SYS@book> alter trigger SYS.TRI_PREVENT_DROP_TABLE disable ;
Trigger altered.

SYS@book> alter trigger TRI_PREVENT_DROP_TRUNCATE disable ;
Trigger altered.

SCOTT@book> alter table ERR$DP008600070001 enable table lock;
Table altered.

SCOTT@book> drop table ERR$DP008600070001 purge ;
Table dropped.

[20170421]impdp SKIP_CONSTRAINT_ERRORS

相關文章