oracle impdp的skip_constraint_errors選項跳過唯一約束錯誤
以前遇到impdp匯入到已經存在的表有唯一索引,且要匯入的資料跟現在資料有唯一衝突.一般處理方法是先把唯一索引刪掉,匯入後再刪除重複資料再重建索引,或者把表匯入為另一個表名,然後再進行關聯匯入.oracle 11g開始impdp加了一個data_ooptions引數,引數目前的選項只有skip_constraint_errors,用於在匯入時忽略非延遲約束繼續執行.
以下測試:
oracle version :11.2.0.4
[oracle@ct6605 ~]$ ORACLE_SID=ct66
[oracle@ct6605 ~]$ sqlplus / as sysdba
#建測試表
SQL> create table scott.t_source as select * from dba_objects;
#建匯入匯出目錄
SQL> create or replace directory home_dump as '/home/oracle';
SQL> exit
#匯出測試資料
[oracle@ct6605 ~]$ expdp system dumpfile=home_dump:expdp_t_source.dmp tables=scott.t_source
Export: Release 11.2.0.4.0 - Production on Fri Mar 25 11:28:56 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=home_dump:expdp_t_source.dmp tables=scott.t_source
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_SOURCE" 8.395 MB 86527 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/expdp_t_source.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 25 11:29:03 2016 elapsed 0 00:00:04
[oracle@ct6605 ~]$ sqlplus / as sysdba
#建目標表
SQL> create table scott.t_dest as select * from dba_objects where rownum<10;
SQL> update scott.t_dest set object_id=object_id+2000000;
SQL> insert into scott.t_dest select * from dba_objects where rownum<10;
SQL> commit;
#建唯一索引
SQL> create unique index idx_t_dest on scott.t_dest(object_id);
SQL> exit
#impdp不加skip_constraint_errors時報錯
[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append
Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:34:45 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T_DEST" 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"."T_DEST" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SYS.IDX_T_DEST) violated
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Mar 25 11:34:51 2016 elapsed 0 00:00:04
#impdp加skip_constraint_errors,並且匯入日誌中顯示總共多少行,匯入了多少行,多少行因為什麼約束沒匯入
[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append data_options=skip_constraint_errors
Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:36:55 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append data_options=skip_constraint_errors
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T_DEST" 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"."T_DEST" 8.395 MB 86518 out of 86527 rows
9 row(s) were rejected with the following error:
ORA-00001: unique constraint (SYS.IDX_T_DEST) violated
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Mar 25 11:37:05 2016 elapsed 0 00:00:07
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28539951/viewspace-2063909/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle主鍵約束、唯一鍵約束、唯一索引的區別(轉)Oracle索引
- Oracle唯一約束中NULL的處理OracleNull
- SQL Server唯一約束的使用SQLServer
- PostgreSQL唯一約束如何使用?SQL
- 【INDEX】Oracle中主鍵、唯一約束與唯一索引之區別IndexOracle索引
- 建立Oracle唯一約束,忽略已有的重複值Oracle
- mysql主從跳過錯誤MySql
- 唯一性約束和唯一性索引的區別索引
- 聊聊Oracle外來鍵約束(Foreign Key)的幾個操作選項Oracle
- Oracle約束Oracle
- oracle 約束Oracle
- [20170421]impdp SKIP_CONSTRAINT_ERRORSAIError
- oracle中的約束Oracle
- NULL和唯一約束UNIQUE的對應關係Null
- Oracle定義約束 外來鍵約束Oracle
- oracle的延遲約束Oracle
- MySQL·捉蟲動態·唯一鍵約束失效MySql
- 【IMPDP】忽略匯入過程中違反約束的資料——DATA_OPTIONS引數
- 【PK】Oracle 10g刪除主鍵約束後無法刪除唯一約束索引問題的模擬與分析Oracle 10g索引
- Oracle 10g expdp/impdp的CONTENT選項體驗Oracle 10g
- oracle 約束詳解Oracle
- Oracle約束簡介Oracle
- oracle鍵約束控制Oracle
- 修改oracle的約束欄位Oracle
- iOS 支付前登入要有跳過選項iOS
- ORACLE: 查詢(看)表的主鍵、外來鍵、唯一性約束和索引Oracle索引
- Mysql-基本練習(06-唯一約束、外來鍵約束、新增、刪除單列)MySql
- exp/imp出現錯誤通過expdp/impdp來解決
- impdp時parallel=4導致的錯誤Parallel
- 【SQL】15 SQL 約束(Constraints)、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束SQLAINull
- oracle 註釋和約束Oracle
- 新增/刪除約束(Oracle)Oracle
- oracle 約束的novalidate 應用Oracle
- mysql多源複製跳過錯誤處理方法MySql
- PostgreSQL違反唯一約束的插入操作會產品HEAP垃圾嗎?SQL
- 錯誤檔名稱下的Oracle選擇Oracle
- oracle 10g impdp與imp 引數選項不同地方Oracle 10g
- oracle外來鍵約束的總結Oracle