Oracle 11gR2 Impdp DATA_OPTIONS SKIP_CONSTRAINT_ERRORS: How to Skip Constraint Errors during Import?


DATA_OPTIONS parameter comes with impdp utility. By default this parameter is disabled during the import. You have to invoke it specifically to handle some special kind of data during the import operations.


There are 2 options for this parameter.

DATA_OPTIONS = [DISABLE_APPEND_HINT | SKIP_CONSTRAINT_ERRORS]
Value 1: DISABLE_APPEND_HINT
          This vale tells the impdp not to use the APPEND hint during the import operation. By default APPEND hint will be enabled in impdp. If you disable this hint by using this option will help you to access the importing tables by other sessions concurrently.

Value 2: SKIP_CONSTRAINT_ERRORS
          This page I wanted to elaborate more on this parameter value SKIP_CONSTRAINT_ERRORS. In normal case the impdp utility will rollback the entire table import in case any constraint error encounters on that particular table. If you use SKIP_CONSTRAINT_ERRORS  as the parameter value, it will tell the import operation to continue even there is constraint errors for some records.  It will import the records which satisfies the constraints.

See example for more details.

I have a table called EMP with 12 number of records.

SQL> select count(*) from emp;

  COUNT(*)
----------
        12

Adding a unique constraint to the table
SQL> alter table emp add constraint emp_ename unique(ename) ;

Table altered.

I have taken the export of this table and I deleting 2 records from that.

SQL> delete from emp where rownum<3;

2 rows deleted.

SQL> commit;

Commit complete.

Importing the dump with table_exists_action=append. This operation is getting failed with constraint error. It will not insert any of the rows from the export dump. It will rollback the entire table import operation.
$ impdp job_name=schemaexp1 full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=EXP_DIR table_exists_action=append

Import: Release 11.2.0.2.0 - Production on Wed Nov 7 02:22:58 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SCHEMAEXP1" successfully loaded/unloaded
Starting "SYS"."SCHEMAEXP1":  /******** AS SYSDBA job_name=schemaexp1 full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=EXP_DIR table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMP" 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"."EMP" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-00001: unique constraint (SCOTT.EMP_ENAME) violated
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SCHEMAEXP1" completed with 1 error(s) at 02:23:02

In below import I am trying to import the dump with DATA_OPTIONS = skip_constraint_errors which will import two rows. These records already deleted from the table.

$ impdp job_name=schemaexp1 full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=EXP_DIR table_exists_action=append DATA_OPTIONS=skip_constraint_errors

Import: Release 11.2.0.2.0 - Production on Wed Nov 7 02:23:21 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SCHEMAEXP1" successfully loaded/unloaded
Starting "SYS"."SCHEMAEXP1":  /******** AS SYSDBA job_name=schemaexp1 full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=EXP_DIR table_exists_action=append DATA_OPTIONS=skip_constraint_errors
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMP" 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"."EMP"                             5.617 KB       2 out of 12 rows
10 row(s) were rejected with the following error:
ORA-00001: unique constraint (SCOTT.EMP_ENAME) violated

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SCHEMAEXP1" successfully completed at 02:23:26


SQL> select count(*) from emp;

  COUNT(*)
----------
        12
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google