Sunday, March 1, 2015

SKIP_CONSTRAINT_ERRORS: How to Skip Constraint Errors during Import.

bash-3.2$ impdp  impdp_test/impdp_test@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND

Import: Release 11.2.0.3.0 - Production on Sun Mar 1 17:41:31 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "IMPDP_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "IMPDP_TEST"."SYS_IMPORT_FULL_01":  impdp_test/********@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"IMPDP_TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "IMPDP_TEST"."IMP_DATA" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "IMPDP_TEST"."IMP_DATA" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (IMPDP_TEST.PK_ID) violated
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "IMPDP_TEST"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 17:41:34

bash-3.2$ impdp  impdp_test/impdp_test@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND ignore=Y
LRM-00112: multiple values not allowed for parameter 'table_exists_action'

Import: Release 11.2.0.3.0 - Production on Sun Mar 1 17:44:40 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
LRM-00112: multiple values not allowed for parameter 'table_exists_action'

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "IMPDP_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "IMPDP_TEST"."SYS_IMPORT_FULL_01":  impdp_test/********@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND table_exists_action=append
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"IMPDP_TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "IMPDP_TEST"."IMP_DATA" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "IMPDP_TEST"."IMP_DATA" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (IMPDP_TEST.PK_ID) violated
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "IMPDP_TEST"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 17:44:41

bash-3.2$ impdp  impdp_test/impdp_test@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=skip_constraint_errors

Import: Release 11.2.0.3.0 - Production on Sun Mar 1 17:46:12 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "IMPDP_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "IMPDP_TEST"."SYS_IMPORT_FULL_01":  impdp_test/********@ORTBDB directory=SBS_DIR dumpfile=testimpdp.dmp logfile=testimpdpda.log TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=skip_constraint_errors
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"IMPDP_TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "IMPDP_TEST"."IMP_DATA" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "IMPDP_TEST"."IMP_DATA"                     5.445 KB       1 out of 2 rows
1 row(s) were rejected with the following error:
ORA-00001: unique constraint (IMPDP_TEST.PK_ID) violated

Rejected rows with the primary keys are:
 Rejected row #1:
   column ID: 10
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "IMPDP_TEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 17:46:18

bash-3.2$

No comments:

Post a Comment