【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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- Oracle 11g impdp 先資料後索引匯入方法Oracle索引
- 19c資料庫impdp匯入view時hang住資料庫View
- Oracle 28.6資料庫impdp匯入view時hang@11Oracle資料庫View
- Redis資料匯入工具優化過程總結Redis優化
- ORA-02291: 違反完整約束條件 (*) - 未找到父項關鍵字
- mysql 匯入匯出資料庫以及函式、儲存過程的介紹MySql資料庫函式儲存過程
- DG配置過程中的引數解釋
- oracle10g expdp資料泵的bug,按schema匯出，匯入impdp時無jobOracle
- IMPDP匯入沒有某個表空間的處理方法__加上引數TRANSFORM就可以了ORM
- Oracle 12.1.0.2 impdp匯入慢診斷Oracle
- 資料庫常用約束資料庫
- mysql資料庫約束MySql資料庫
- [20200620]expdp impdp exclude引數.txt
- 資料庫中欄位資料型別以及約束資料庫資料型別
- ORACLE 資料泵impdp匯入報錯之ORA-31693 ORA-04098Oracle
- mysql儲存過程的引數MySql儲存過程
- Oracle 12c expdp和impdp匯出匯入表Oracle
- Hive資料匯入HBase引起資料膨脹引發的思考Hive
- 資料型別與約束資料型別
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- expdp/impdp 詳細引數解釋
- 中介軟體的引數解析過程
- 透過 ETL 匯出 Hive 中的資料Hive
- MySQL的寫入資料儲存過程MySql儲存過程
- impdp和expdp用法及引數介紹
- 【SQL】15 SQL 約束（Constraints）、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束SQLAINull
- springboot引導上下文載入過程和配置載入過程Spring Boot
- MongoDB--Mongodb 中資料匯出與匯入MongoDB
- Elasticsearch 如何保證寫入過程中不丟失資料的Elasticsearch
- 用Keras中的權值約束緩解過擬合Keras
- Flume：資料匯入到hdfs中
- Java：匯出Excel大批量資料的優化過程JavaExcel優化
- 厲害了！12秒將百萬資料透過EasyExcel匯入MySQL資料庫中ExcelMySql資料庫
- Mysql入門【Mysql約束】MySql
- 資料匯入終章：如何將HBase的資料匯入HDFS？