advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment