Oracle impdp的skip_constraint_errors選項跳過唯一約束錯誤

Oracle impdp的skip_constraint_errors選項跳過唯一約束錯誤

以前遇到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