【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官方文件中的描述請參考如下連結。
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 --
透過一個具體的演示展現一下這個選項的基本用法。
1.幫助文件中的說明
1）線上命令列幫助文件
secooler@secDB /home/oracle$ impdp -help
……
DATA_OPTIONS
Data layer option flags.
Valid keywords are: SKIP_CONSTRAINT_ERRORS.
……
2）Oracle官方文件中的描述請參考如下連結。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- impdp做資料匯入時約束和觸發器引起資料匯入後應用故障觸發器
- Impdp資料泵匯入
- 【impdp】IMPDP中的TRANSFORM引數--【資料泵】EXPDP匯出表結構(真實案例)後傳ORM
- 【匯出匯入】IMPDP table_exists_action 引數的應用
- truncate表後impdp匯入該表時加exclude=index引數並不能排除索引資料的匯入Index索引
- 通過EXPDP/IMPDP匯出匯入遠端資料倒本地
- 使用資料泵impdp匯入資料
- 資料泵IMPDP 匯入工具的使用
- mysql資料庫匯入外來鍵約束問題MySql資料庫
- AWR資料的匯出和匯入全過程
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- 通過大容量匯入操作控制約束檢查
- IMPDP匯入遠端資料庫資料庫
- impdp匯入時使用table_exists_action引數的區別
- PostgreSQL違反唯一約束的插入操作會產品HEAP垃圾嗎？SQL
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- eclipse匯入約束檔案Eclipse
- 使用impdp network link 跳過expdp直接匯入資料
- 【匯入匯出】資料泵 job_name引數的使用
- expdp impdp 資料庫匯入匯出命令詳解資料庫
- 【IMPDP】當匯入資料時遭遇表已存時的應對手段——TABLE_EXISTS_ACTION引數
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- impdp匯入時remap_*引數與table_exists_action的關係REM
- 【IMPDP】使用IMPDP自動建立使用者並完成資料的匯入
- 用impdp匯入資料的一次經歷
- 10G資料泵載入命令expdp/impdp的引數說明
- 資料匯入imp過程中遇到TNS-12592的錯誤
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- impdp匯入資料後,system 表空間整理
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- MYSQL匯入中斷處理過程MySql
- Redis資料匯入工具優化過程總結Redis優化
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- 【impdp】使用impdp工具排除特定表的匯入
- 關於資料泵impdp引數驗證（一）
- rac環境下使用impdp匯入資料出錯
- impdp hangs，慎用impdp parallel引數Parallel