[杂七杂八的小问题]impdp外键没有导入的问题

0. summary

1. 问题背景和初步分析
2. 问题模拟
.   2.1 环境准备
.   2.2 模拟导入父表的数据
.   2.3 模拟导入子表的数据
.   2.4 模拟导入父表和子表的数据
3. 结论

1. 问题背景和初步分析

应用一套系统需要迁移，迁移的内容包含指定的用户下的所有对象，其中部分用户下的表只需要导指定的数据。想着方便点，就先导出metadata, 然后将需要数据的表单独用parfile导出，导入的时候先导入metadata保证全部对象存在，再导入需要数据的表，使用replace的选项。没有使用metadata+data_only的原因是导入时需要关闭外键，再打开，验证很耗时，虽然不验证一般也不会有问题。

测试过程中没有报错，但是应用在检查过程中，发现有两个外键没有导入。使用了应用提供的语句查看了下确实如此。

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，然后导入自己的测试库，发现约束是有的。然后再尝试导入需要数据的表时，在没有导完时，发现这两个外键约束没有了，意识到可能是导入引起的问题。我们知道，在使用impdp完成数据库导入时，若表已经存在，有四种的处理方式：

  • skip : 默认操作
  • replace : 先drop表，然后创建表，最后插入数据
  • append : 在原来数据的基础上增加数据
  • truncate : 先truncate, 然后再插入数据

我使用的是replace操作，导入的时候会删除原表，为什么不使用truncate, 使用truncate和使用结构+数据的方式实质是一样的，同样需要关闭外键。仔细检查需要导出的表清单以及该约束所属的对象，发现一个问题，应用需要导入父表的数据，而子表的数据不需要导入，那么是不是drop动作引起的，我尝试了下手工drop父表，是无法drop的。而replace可以强制删除掉。

2. 问题模拟

2.1 环境准备

#### 创建子表 ####

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

#### 创建父表 ####

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

#### 创建父表的主键 ####

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);

#### 表数据 ####

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

成功导入，检查下约束，发现确实没有了。

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

发现仅导入父表时，truncate是可以的，检查下表数据和约束状态正常。

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

发现仅导入父表的情况下，检查下表数据和约束状态，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

发现数据虽然导入了，但是外键约束失败了。

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

无法执行，同样是因为父表无数据，当然，这里不存在删表，所以约束还在。

#### 使用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是要导出数据的表清单，我这里表是全部都要导入结构的，如果有表的表结构也不需要导入，需要再准备个表结构清单表。对于这种问题，图省事可以在导入完成后再把这些约束加上。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 160,165评论 4赞 364
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,720评论 1赞 298
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 109,849评论 0赞 244
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,245评论 0赞 213
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,596评论 3赞 288
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,747评论 1赞 222
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,977评论 2赞 315
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,708评论 0赞 204
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,448评论 1赞 246
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,657评论 2赞 249
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,141评论 1赞 261
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,493评论 3赞 258
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,153评论 3赞 238
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,108评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,890评论 0赞 198
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,799评论 2赞 277
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,685评论 2赞 272

推荐阅读更多精彩内容