[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

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 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，就可以防止删除。链接

--//

--//建立如下触发器：

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.