- 23 Nov, 2023
- 0 Comments
- 2 Mins Read
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.
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 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 THU NOV 23 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 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 THU NOV 23 10:33:07 2017 elapsed 0 00:00:07