[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找到如下链接：
http://db-oriented.com/2014/07/19/impdp-which-rows-failed/
--//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.
