- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPENDWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPENDProcessing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCEProcessing object type TABLE_EXPORT/TABLE/TABLETable "DBATEST"."GNSRT00" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of appendProcessing object type TABLE_EXPORT/TABLE/TABLE_DATAORA-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) violatedJob "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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND data_options=SKIP_CONSTRAINT_ERRORSStarting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND data_options=SKIP_CONSTRAINT_ERRORSProcessing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCEProcessing object type TABLE_EXPORT/TABLE/TABLETable "DBATEST"."GNSRT00" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of appendProcessing 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) violatedRejected rows with the primary keys are:Rejected row #1:column RRN: 3Rejected row #2:column RRN: 4Processing object type TABLE_EXPORT/TABLE/INDEX/INDEXJob "SYS"."SYS_IMPORT_FULL_01" successfully completed at THU NOV 23 10:33:07 2017 elapsed 0 00:00:07 |