Warning: session_start(): open(/tmp/sess_6l5khnjk5s9gv7j2hjurbhemp6, O_RDWR) failed: No space left on device (28) in /razi/eraazi/darbari/mercy/index.php on line 15
EraAzi | SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp
 

DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS is a very useful parameter mostly used with table_exists_action=APPEND .

In general, if you are appending data ( table_exists_action=APPEND) to an existing table with a unique constraint and if the new rows violating the unique constraint,
then the import job will fail( even if only one row violates the constraint)

DATA_OPTION=SKIP_CONSTRAINT_ERRORS parameter will help in skipping the duplicate row and import the rest of the rows. It is similar to IGNORE=Y option of classic exp/imp

Here I tried to append data with table_exists_action=APPEND, it failed due to a unique key violation.

impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND

impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND

 
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."GNSRT00" 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 "DBATEST"."GNSRT00" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (DBATEST.RRN_PK_GNSRT00) violated
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Jan 24 10:31:28 2017 elapsed 0 00:00:11
 

Now let us try with SKIP_CONSTRAINT_ERRORS option:

impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND data_options=SKIP_CONSTRAINT_ERRORS

impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND data_options=SKIP_CONSTRAINT_ERRORS

 
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=unique.dmp directory=EXPDIR  table_exists_action=APPEND data_options=SKIP_CONSTRAINT_ERRORS
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."GNSRT00" 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 "DBATEST"."GNSRT00"                         9.078 KB      20 out of 22 rows  ----- >>> 
2 row(s) were rejected with the following error:
ORA-00001: unique constraint (DBATEST.RRN_PK_GNSRT00) violated
 
Rejected rows with the primary keys are:
 Rejected row #1:
   column RRN: 3
 Rejected row #2:
   column RRN: 4
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jan 24 10:33:07 2017 elapsed 0 00:00:07
 

We can see, it has rejected the conflicting rows and imported the rest of the data.

BACKUP & RECOVERY