The skip_constraint_errors option of Oracle impdp skips the unique constraint error.

The skip_constraint_errors option of Oracle impdp skips the unique constraint error.

In the past, impdp imported to an existing table has a unique index, and the data to be imported has a unique conflict with the current data. the general solution is to first Delete the unique index, import the duplicate data, and then re-create the index, or import the table as another table name, and then import the joined table. at the beginning of Oracle 11g, impdp added a data_ooptions parameter. Currently, the parameter only has skip_constraint_errors, which is used to ignore non-delay constraints during import and continue execution.

The following tests:
Oracle version: 11.2.0.4

[Oracle @ ct6605 ~] $ ORACLE_SID = ct66
[Oracle @ ct6605 ~] $ Sqlplus/as sysdba

# Create a test table
SQL> create table scott. t_source as select * from dba_objects;

# Create an import/export directory
SQL> create or replace directory home_dump as '/home/oracle ';
SQL> exit

# Export Test Data
[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,201 1, 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
.. 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
# Create a target table
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;
# Create a unique index
SQL> create unique index idx_t_dest on scott. t_dest (object_id );
SQL> exit

# An error is reported when impdp does not add 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,201 1, 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 "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 adds skip_constraint_errors and displays the total number of rows, number of rows imported, and number of rows in the import log because of any constraints
[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,201 1, 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: Repeated remap_table = scott. t_source: t_dest table_exists_action = append data_options = begin
Processing object type TABLE_EXPORT/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