在我们完成数据导入的过程中，如果出现了违反约束（严格的讲是非延迟约束），默认情况下将无法完成数据的导入。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 --
【IMPDP】忽略导入过程中违反约束的数据——DATA_OPTIONS参数
分享好友
分享这个小栈给你的朋友们，一起进步吧。
订阅须知
• 所有用户可根据关注领域订阅专区或所有专区
• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询
• 专区发布评论属默认订阅所评论专区（除付费小栈外）