Oracle impdp的skip_constraint_errors选项跳过唯一约束错误
XMM00 2016-03-29
以前遇到impdp导入到已经存在的表有唯一索引,且要导入的数据跟现在数据有唯一冲突.一般处理方法是先把唯一索引删掉,导入后再删除重复数据再重建索引,或者把表导入为另一个表名,然后再进行关联导入.Oracle 11g开始impdp加了一个data_ooptions参数,参数目前的选项只有skip_constraint_errors,用于在导入时忽略非延迟约束继续执行.
以下测试:
oracle version :11.2.0.4
[oracle@ct6605 ~]$ ORACLE_SID=ct66
[oracle@ct6605 ~]$ sqlplus / as sysdba
#建测试表
SQL> create table scott.t_source as select * from dba_objects;
#建导入导出目录
SQL> create or replace directory home_dump as '/home/oracle';
SQL> exit
#导出测试数据
[oracle@ct6605 ~]$ expdp system dumpfile=home_dump:expdp_t_source.dmp tables=scott.t_source
Export: Release 11.2.0.4.0 - Production on Fri Mar 25 11:28:56 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
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 "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=home_dump:expdp_t_source.dmp tables=scott.t_source
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_SOURCE" 8.395 MB 86527 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/expdp_t_source.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 25 11:29:03 2016 elapsed 0 00:00:04
[oracle@ct6605 ~]$ sqlplus / as sysdba
#建目标表
SQL> create table scott.t_dest as select * from dba_objects where rownum<10;
SQL> update scott.t_dest set object_id=object_id+2000000;
SQL> insert into scott.t_dest select * from dba_objects where rownum<10;
SQL> commit;
#建唯一索引
SQL> create unique index idx_t_dest on scott.t_dest(object_id);
SQL> exit
#impdp不加skip_constraint_errors时报错
[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append
Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:34:45 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T_DEST" 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_DEST" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SYS.IDX_T_DEST) violated
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Mar 25 11:34:51 2016 elapsed 0 00:00:04
#impdp加skip_constraint_errors,并且导入日志中显示总共多少行,导入了多少行,多少行因为什么约束没导入
[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append data_options=skip_constraint_errors
Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:36:55 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append data_options=skip_constraint_errors
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T_DEST" 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_DEST" 8.395 MB 86518 out of 86527 rows
9 row(s) were rejected with the following error:
ORA-00001: unique constraint (SYS.IDX_T_DEST) violated
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Mar 25 11:37:05 2016 elapsed 0 00:00:07
