【IMPDP】忽略导入过程中违反约束的数据——DATA_OPTIONS参数

在我们完成数据导入的过程中，如果出现了违反约束（严格的讲是非延迟约束），默认情况下将无法完成数据的导入。11g IMPDP提供了DATA_OPTIONS选项可以比较方便的解决这个问题。
通过一个具体的演示展现一下这个选项的基本用法。

1.帮助文档中的说明
1）在线命令行帮助文档
secooler@secDB /home/oracle$ impdp -help
……
DATA_OPTIONS
Data layer option flags.
Valid keywords are: SKIP_CONSTRAINT_ERRORS.
……

2）Oracle官方文档中的描述请参考如下链接。
http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/dp_import.htm#SUTIL906

2.演示一下这个选项的作用
1）创建两张测试表t_parent和t_child
sec@11gR2> create table t_parent (parent_id int primary key, name varchar2(10));

Table created.

sec@11gR2> insert into t_parent values (1,'secooler1');

1 row created.

sec@11gR2> commit;

Commit complete.

sec@11gR2> select * from t_parent;

 PARENT_ID NAME
---------- ------------------------------
         1 secooler1

sec@11gR2> create table t_child (child1_id int primary key, parent_id int);

Table created.

sec@11gR2> insert into t_child values (1,1);

1 row created.

sec@11gR2> insert into t_child values (2,2);

1 row created.

sec@11gR2> commit;

Commit complete.

sec@11gR2> select * from t_child;

 CHILD1_ID  PARENT_ID
---------- ----------
         1          1
         2          2

注意，此时创建的测试表没有主外键参照关系。

2）使用EXPDP工具生成T_CHILD表的逻辑备份
secooler@secDB /db_backup/dpump_dir$ expdp sec/sec directory=dpump_dir dumpfile=sec.dmp TABLES=sec.t_child

Export: Release 11.2.0.1.0 - Production on Fri Jun 17 23:00:49 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01":  sec/******** directory=dpump_dir dumpfile=sec.dmp TABLES=sec.t_child
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SEC"."T_CHILD"                             5.460 KB       2 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
  /db_backup/dpump_dir/sec.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 23:01:04

3）删除表T_CHILD的数据并添加外键约束
sys@11gR2> conn sec/sec
Connected.
sec@11gR2> delete from t_child;

2 rows deleted.

sec@11gR2> commit;

Commit complete.

sec@11gR2> alter table t_child add constraint FK_t_child foreign key (parent_id) references t_parent (parent_id) on delete cascade;

Table altered.

sec@11gR2> select * from t_parent;

 PARENT_ID NAME
---------- ------------------------------
         1 secooler1

sec@11gR2> select * from t_child;

no rows selected

4）使用刚刚生成的备份文件sec.dmp完成数据的导入，此时不是使用DATA_OPTIONS选项
（1）不是使用DATA_OPTIONS选项完成数据的导入
secooler@secDB /db_backup/dpump_dir$ impdp sec/sec directory=dpump_dir dumpfile=sec.dmp TABLES=sec.t_child TABLE_EXISTS_ACTION=TRUNCATE

Import: Release 11.2.0.1.0 - Production on Fri Jun 17 23:44:23 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SEC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SEC"."SYS_IMPORT_TABLE_01":  sec/******** directory=dpump_dir dumpfile=sec.dmp TABLES=sec.t_child TABLE_EXISTS_ACTION=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "SEC"."T_CHILD" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "SEC"."T_CHILD" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (SEC.FK_T_CHILD) violated - parent key not found
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SEC"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 23:44:28

注意上面的“ORA-02291”报错，表明违反了外键约束。

（2）验证数据是否被导入
sys@11gR2> conn sec/sec
Connected.
sec@11gR2> select * from t_parent;

 PARENT_ID NAME
---------- ------------------------------
         1 secooler1

sec@11gR2> select * from t_child;

no rows selected

可见，在未使用DATA_OPTIONS选项的情况下T_CHILD表数据未完成导入。

5）使用刚刚生成的备份文件sec.dmp完成数据的导入，此时使用DATA_OPTIONS选项的SKIP_CONSTRAINT_ERRORS参数
（1）使用DATA_OPTIONS选项的SKIP_CONSTRAINT_ERRORS参数完成数据的导入
secooler@secDB /db_backup/dpump_dir$ impdp sec/sec directory=dpump_dir dumpfile=sec.dmp TABLES=sec.t_child TABLE_EXISTS_ACTION=TRUNCATE DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

Import: Release 11.2.0.1.0 - Production on Fri Jun 17 23:45:32 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SEC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SEC"."SYS_IMPORT_TABLE_01":  sec/******** directory=dpump_dir dumpfile=sec.dmp TABLES=sec.t_child TABLE_EXISTS_ACTION=TRUNCATE DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "SEC"."T_CHILD" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T_CHILD"                             5.460 KB       1 out of 2 rows
1 row(s) were rejected with the following error:
ORA-02291: integrity constraint (SEC.FK_T_CHILD) violated - parent key not found

Rejected rows with the primary keys are:
 Rejected row #1:
   column CHILD1_ID: 2
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SEC"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 23:45:45

从导入日志中可以看出2条数据中的一条成功的导入到了T_CHILD表，出错数据的信息亦有体现。

（2）验证导入的数据
sys@11gR2> conn sec/sec
Connected.
sec@11gR2> select * from t_parent;

 PARENT_ID NAME
---------- ------------------------------
         1 secooler1

sec@11gR2> select * from t_child;

 CHILD1_ID  PARENT_ID
---------- ----------
         1          1

的确，满足约束的记录已经成功的导入到了T_CHILD表中。

3.小结
本文通过实验的方法体验了一下DATA_OPTIONS参数的作用，慢慢体会吧。

Good luck.

secooler
10.06.17

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-665592/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-665592/