[雜七雜八的小問題]impdp外鍵沒有導入的問題

0. summary

1. 問題背景和初步分析
2. 問題模擬
.   2.1 環(huán)境準備
.   2.2 模擬導入父表的數據
.   2.3 模擬導入子表的數據
.   2.4 模擬導入父表和子表的數據
3. 結論

1. 問題背景和初步分析

應用一套系統(tǒng)需要遷移，遷移的內容包含指定的用戶下的所有對象祷舀，其中部分用戶下的表只需要導指定的數據瀑梗。想著方便點，就先導出metadata, 然后將需要數據的表單獨用parfile導出裳扯，導入的時候先導入metadata保證全部對象存在抛丽，再導入需要數據的表，使用replace的選項饰豺。沒有使用metadata+data_only的原因是導入時需要關閉外鍵铺纽，再打開，驗證很耗時哟忍，雖然不驗證一般也不會有問題狡门。

測試過程中沒有報錯，但是應用在檢查過程中锅很，發(fā)現有兩個外鍵沒有導入其馏。使用了應用提供的語句查看了下確實如此。

select * from dba_constraints a where a.constraint_name in ('FK_ALARM_LH_REFERENCE_ALARM_TY','FK_ALARM_LO_REFERENCE_ALARM_TY');

從約束的last_change時間來看爆安，不會是新建的叛复。首先我嘗試了下重新導出個metadata，然后導入自己的測試庫，發(fā)現約束是有的褐奥。然后再嘗試導入需要數據的表時咖耘，在沒有導完時，發(fā)現這兩個外鍵約束沒有了撬码，意識到可能是導入引起的問題儿倒。我們知道，在使用impdp完成數據庫導入時呜笑，若表已經存在夫否，有四種的處理方式：

skip : 默認操作
replace : 先drop表，然后創(chuàng)建表叫胁，最后插入數據
append : 在原來數據的基礎上增加數據
truncate : 先truncate, 然后再插入數據

我使用的是replace操作凰慈，導入的時候會刪除原表，為什么不使用truncate, 使用truncate和使用結構+數據的方式實質是一樣的驼鹅，同樣需要關閉外鍵微谓。仔細檢查需要導出的表清單以及該約束所屬的對象，發(fā)現一個問題输钩，應用需要導入父表的數據豺型，而子表的數據不需要導入，那么是不是drop動作引起的张足，我嘗試了下手工drop父表触创，是無法drop的。而replace可以強制刪除掉为牍。

2. 問題模擬

2.1 環(huán)境準備

#### 創(chuàng)建子表 ####

create table fk_t as select * from user_objects;
delete from fk_t where object_id is null;
commit;

#### 創(chuàng)建父表 ####

create table pk_t as select * from user_objects;
delete from pk_t where object_id is null;
commit;

#### 創(chuàng)建父表的主鍵 ####

alter table PK_t add constraint pk_pktable primary key (OBJECT_ID);

#### 創(chuàng)建子表的外鍵 ####

alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);

#### 表數據 ####

PANGZI@panda>select count(*) from pk_t;

  COUNT(*)
----------
         3

PANGZI@panda>select count(*) from fk_t;

  COUNT(*)
----------
         2

2.2 模擬導入父表的數據

#### 導出父表和子表的數據 ####

expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=exp_test.log tables=pangzi.pk_t,pangzi.fk_t;

#### 刪除父表和子表數據哼绑，相當于表結構已經存在沒有數據的情況 ####

PANGZI@panda>truncate table fk_t;

Table truncated.

PANGZI@panda>truncate table pk_t;
truncate table pk_t
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


PANGZI@panda>alter table fk_t disable constraint fk_fktable;

Table altered.

PANGZI@panda>truncate table pk_t;

Table truncated.

PANGZI@panda>alter table fk_t enable constraint fk_fktable;

Table altered.

PANGZI@panda>col owner for a30
PANGZI@panda>select owner, constraint_name, table_name, status
  2    from dba_constraints a
  3   where a.constraint_name in upper('fk_fktable');

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PANGZI                         FK_FKTABLE                     FK_T                           ENABLED

#### 使用replace導入 ####

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.pk_t table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 14:55:39 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.pk_t table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PANGZI"."PK_T"                             10.52 KB       3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Mar 7 14:55:41 2017 elapsed 0 00:00:02

成功導入，檢查下約束碉咆，發(fā)現確實沒有了抖韩。

PANGZI@panda>select owner, constraint_name, table_name, status
  2    from dba_constraints a
  3   where a.constraint_name in upper('fk_fktable');

no rows selected

這是正常的，因為正常情況下疫铜，drop父表會報錯茂浮，而replace會強制刪除父表，父表沒有了壳咕，那么約束也就沒用了席揽。

#### 使用truncate導入 ####

drop table fk_t purge;
drop table pk_t purge;
create table fk_t as select * from user_objects where 1=2;
create table pk_t as select * from user_objects where 1=2;
alter table PK_t add constraint pk_pktable primary key (OBJECT_ID);
alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.pk_t table_exists_action=truncate

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 15:03:27 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.pk_t table_exists_action=truncate 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "PANGZI"."PK_T" 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 "PANGZI"."PK_T"                             10.52 KB       3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Mar 7 15:03:30 2017 elapsed 0 00:00:03

發(fā)現僅導入父表時，truncate是可以的谓厘，檢查下表數據和約束狀態(tài)正常幌羞。

PANGZI@panda>select count(*) from pk_t;

  COUNT(*)
----------
         3

PANGZI@panda>select count(*) from fk_t;

  COUNT(*)
----------
         0

PANGZI@panda>select owner, constraint_name, table_name, status
  2    from dba_constraints a
  3   where a.constraint_name in upper('fk_fktable');

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PANGZI                         FK_FKTABLE                     FK_T                           ENABLED

#### 使用append導入 ####

drop table fk_t purge;
drop table pk_t purge;
create table fk_t as select * from user_objects where 1=2;
create table pk_t as select * from user_objects where 1=2;
alter table PK_t add constraint pk_pktable primary key (OBJECT_ID);
alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.pk_t table_exists_action=truncate

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 15:03:27 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.pk_t table_exists_action=truncate 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "PANGZI"."PK_T" 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 "PANGZI"."PK_T"                             10.52 KB       3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Mar 7 15:03:30 2017 elapsed 0 00:00:03

發(fā)現僅導入父表的情況下，檢查下表數據和約束狀態(tài)竟稳，append同樣不存在問題属桦。

PANGZI@panda>select count(*) from pk_t;

  COUNT(*)
----------
         3

PANGZI@panda>select count(*) from fk_t;

  COUNT(*)
----------
         0

PANGZI@panda>select owner, constraint_name, table_name, status
  2    from dba_constraints a
  3   where a.constraint_name in upper('fk_fktable');

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PANGZI                         FK_FKTABLE                     FK_T                           ENABLED

2.3 模擬導入子表的數據

#### 使用replace導入 ####

drop table fk_t purge;
drop table pk_t purge;
create table fk_t as select * from user_objects where 1=2;
create table pk_t as select * from user_objects where 1=2;
alter table PK_t add constraint pk_pktable primary key (OBJECT_ID);
alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 15:10:22 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PANGZI"."FK_T"                             10.45 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (PANGZI.FK_FKTABLE) - parent keys not found
Failing sql is:
ALTER TABLE "PANGZI"."FK_T" ADD CONSTRAINT "FK_FKTABLE" FOREIGN KEY ("OBJECT_ID") REFERENCES "PANGZI"."PK_T" ("OBJECT_ID") ENABLE
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Mar 7 15:10:24 2017 elapsed 0 00:00:01

發(fā)現數據雖然導入了熊痴，但是外鍵約束失敗了。

PANGZI@panda>select count(*) from pk_t;

  COUNT(*)
----------
         0

PANGZI@panda>select count(*) from fk_t;

  COUNT(*)
----------
         2

PANGZI@panda>select owner, constraint_name, table_name, status
  2    from dba_constraints a
  3   where a.constraint_name in upper('fk_fktable');

no rows selected

PANGZI@panda>alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);
alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID)
                                *
ERROR at line 1:
ORA-02298: cannot validate (PANGZI.FK_FKTABLE) - parent keys not found

這種情況很好理解聂宾，因為父表沒數據果善，當然無法建立外鍵。

#### 使用truncate導入 ####

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t table_exists_action=truncate

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 15:12:32 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t table_exists_action=truncate 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "PANGZI"."FK_T" 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 "PANGZI"."FK_T" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (PANGZI.FK_FKTABLE) violated - parent key not found
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Mar 7 15:12:33 2017 elapsed 0 00:00:01

無法執(zhí)行系谐，同樣是因為父表無數據巾陕，當然，這里不存在刪表蔚鸥，所以約束還在惜论。

#### 使用append導入 ####

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t table_exists_action=append

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 15:37:47 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t table_exists_action=append 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "PANGZI"."FK_T" 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 "PANGZI"."FK_T" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (PANGZI.FK_FKTABLE) violated - parent key not found
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Mar 7 15:37:49 2017 elapsed 0 00:00:01

append也是一個道理许赃。

2.4 模擬導入父表和子表的數據

#### 使用replace導入 ####

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t,pangzi.pk_t table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 15:39:24 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t,pangzi.pk_t table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PANGZI"."FK_T"                             10.45 KB       2 rows
. . imported "PANGZI"."PK_T"                             10.52 KB       3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Mar 7 15:39:26 2017 elapsed 0 00:00:01

這種情況下止喷，replace沒有任何問題，數據和約束都正常混聊，這也是常用的導入方式弹谁。

PANGZI@panda>select count(*) from pk_t;

  COUNT(*)
----------
         3

PANGZI@panda>select count(*) from fk_t;

  COUNT(*)
----------
         2

PANGZI@panda>select owner, constraint_name, table_name, status
  2    from dba_constraints a
  3   where a.constraint_name in upper('fk_fktable');

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PANGZI                         FK_FKTABLE                     FK_T                           ENABLED

#### 使用truncate導入 ####

drop table fk_t purge;
drop table pk_t purge;
create table fk_t as select * from user_objects where 1=2;
create table pk_t as select * from user_objects where 1=2;
alter table PK_t add constraint pk_pktable primary key (OBJECT_ID);
alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t,pangzi.pk_t table_exists_action=truncate

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 15:41:45 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t,pangzi.pk_t table_exists_action=truncate 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "PANGZI"."FK_T" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "PANGZI"."PK_T" 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 "PANGZI"."FK_T" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (PANGZI.FK_FKTABLE) violated - parent key not found
. . imported "PANGZI"."PK_T"                             10.52 KB       3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Mar 7 15:41:46 2017 elapsed 0 00:00:01

報錯很明顯，父表的數據導入了句喜，但是子表因為外鍵的關系预愤，數據無法導入，這也是通常情況下為什么不使用結構+數據導入方式的原因咳胃，需要關閉外鍵約束導入植康，導入之后再啟用外鍵，存在是否校驗的問題展懈。比如：

select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||';' 
from dba_constraints WHERE CONSTRAINT_TYPE='R' and owner = upper('crm');

select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' ENABLE NOVALIDATE CONSTRAINT '||constraint_name||';' 
from dba_constraints WHERE CONSTRAINT_TYPE='R' and owner = upper('crm');

#### 使用append導入 ####

[oracle@stb11g pump]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t,pangzi.pk_t table_exists_action=append

Import: Release 11.2.0.4.0 - Production on Tue Mar 7 16:33:51 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR dumpfile=test.dmp logfile=imp_test.log tables=pangzi.fk_t,pangzi.pk_t table_exists_action=append 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "PANGZI"."FK_T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "PANGZI"."PK_T" 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 "PANGZI"."FK_T" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (PANGZI.FK_FKTABLE) violated - parent key not found
. . imported "PANGZI"."PK_T"                             10.52 KB       3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Mar 7 16:33:53 2017 elapsed 0 00:00:01

append也是一樣的

3. 結論

簡單點销睁，在表結構存在的前提下，如果僅導入其中一張表的數據存崖，table_exists_action無論使用什么選項冻记，都可能存在問題（append雖然對導入父表沒有問題，但是整體導入一般不會是僅存在父表的導入来惧，針對其他父子表都需要導入的情況需要關閉外鍵）冗栗。

#### 檢查是否存在導入父表數據而不導入數據的子表的腳本 ####

select dc.owner,
       dc.constraint_name,
       dc.constraint_type,
       dc.table_name,
       dc.status
  from dba_constraints dc,
       (select b.owner, b.constraint_name
          from v_exptab_check a, dba_constraints b
         where a.owner = b.owner
           and a.table_name = b.table_name
           and b.constraint_type = 'P') x,
       v_exptab_check v
 where dc.r_owner = x.owner
   and dc.r_constraint_name = x.constraint_name
   and dc.constraint_type = 'R'
   and dc.owner = v.owner(+)
   and dc.table_name = v.table_name(+)
   and v.owner is null
   and v.table_name is null;

v_exptab_check是要導出數據的表清單，我這里表是全部都要導入結構的供搀，如果有表的表結構也不需要導入隅居，需要再準備個表結構清單表。對于這種問題葛虐，圖省事可以在導入完成后再把這些約束加上胎源。

最后編輯于：2017.12.06 02:31:14

?著作權歸作者所有,轉載或內容合作請聯系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市挡闰，隨后出現的幾起案子乒融，更是在濱河造成了極大的恐慌掰盘，老刑警劉巖，帶你破解...
沈念sama閱讀 218,122評論 6贊 505
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件赞季，死亡現場離奇詭異愧捕，居然都是意外死亡，警方通過查閱死者的電腦和手機申钩，發(fā)現死者居然都...
沈念sama閱讀 93,070評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門次绘，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人撒遣，你說我怎么就攤上這事邮偎。” “怎么了义黎？”我有些...
開封第一講書人閱讀 164,491評論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵禾进，是天一觀的道長。經常有香客問我廉涕，道長泻云，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,636評論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任狐蜕，我火速辦了婚禮宠纯，結果婚禮上，老公的妹妹穿的比我還像新娘层释。我一直安慰自己婆瓜，他們只是感情好，可當我...
茶點故事閱讀 67,676評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布贡羔。她就那樣靜靜地躺著廉白，像睡著了一般。火紅的嫁衣襯著肌膚如雪治力。梳的紋絲不亂的頭發(fā)上蒙秒，一...
開封第一講書人閱讀 51,541評論 1贊 305
城市分裂傳說
那天，我揣著相機與錄音宵统，去河邊找鬼晕讲。笑死，一個胖子當著我的面吹牛马澈，可吹牛的內容都是我干的瓢省。我是一名探鬼主播，決...
沈念sama閱讀 40,292評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼痊班，長吁一口氣：“原來是場噩夢啊……” “哼勤婚！你這毒婦竟也來了？” 一聲冷哼從身側響起涤伐，我...
開封第一講書人閱讀 39,211評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤馒胆，失蹤者是張志新（化名）和其女友劉穎缨称，沒想到半個月后，有當地人在樹林里發(fā)現了一具尸體祝迂，經...
沈念sama閱讀 45,655評論 1贊 314
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡睦尽，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內容為張勛視角年9月15日...
茶點故事閱讀 37,846評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現自己被綠了型雳。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片当凡。...
茶點故事閱讀 39,965評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖纠俭，靈堂內的尸體忽然破棺而出沿量，到底是詐尸還是另有隱情，我是刑警寧澤冤荆，帶...
沈念sama閱讀 35,684評論 5贊 347
?日本核電站爆炸內幕
正文年R本政府宣布朴则，位于F島的核電站，受9級特大地震影響匙赞，放射性物質發(fā)生泄漏佛掖。R本人自食惡果不足惜妖碉，卻給世界環(huán)境...
茶點故事閱讀 41,295評論 3贊 329
男人毒藥：我在死后第九天來索命
文/蒙蒙一涌庭、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧欧宜，春花似錦坐榆、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,894評論 0贊 22
一樁弒父案席镀，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至夏漱，卻和暖如春豪诲，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背挂绰。一陣腳步聲響...
開封第一講書人閱讀 33,012評論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工屎篱，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人葵蒂。一個月前我還...
沈念sama閱讀 48,126評論 3贊 370
代替公主和親
正文我出身青樓交播，卻偏偏與公主長得像，于是被迫代替她去往敵國和親践付。傳聞我的和親對象是個殘疾皇子秦士，可洞房花燭夜當晚...
茶點故事閱讀 44,914評論 2贊 355

[雜七雜八的小問題]impdp外鍵沒有導入的問題

0. summary

1. 問題背景和初步分析

2. 問題模擬

2.1 環(huán)境準備

2.2 模擬導入父表的數據

2.3 模擬導入子表的數據

2.4 模擬導入父表和子表的數據

3. 結論

推薦閱讀更多精彩內容