EXPDP/IMPDP单表住外键

以前都知道在导入单表的时候主外键会有问题，如果主表不存在你自己直接导入子表会出现问题，今天测试了一下

SQL> create table test
  2  as
  3  select * from dba_users;
 
Table created

alter table TEST
add constraint PK_TEST primary key (USER_ID)

SQL> create index ind_test
  2  on test(username);
 
Index created
 
SQL> create table test0
  2  as
  3  select * from v$session;
 

alter table TEST0
add constraint FK_TEST foreign key (USER#)
references TEST (USER_ID);

进行导出

[oracle@b000-vmpomstestdb ~]$ expdp pp/gelc123 tables=test  dumpfile=TEMP_DIR:test20100826.dmp logfile=TEMP_DIR:test20100826.log

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 07 December, 2012 19:25:15

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "PP"."SYS_EXPORT_TABLE_01":  pp/******** tables=test dumpfile=TEMP_DIR:test20100826.dmp logfile=TEMP_DIR:test20100826.log
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/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "PP"."TEST"                                 17.93 KB     101 rows
Master table "PP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PP.SYS_EXPORT_TABLE_01 is:
  /devrman/expdp/test20100826.dmp
Job "PP"."SYS_EXPORT_TABLE_01" successfully completed at 19:25:25

[oracle@b000-vmpomstestdb ~]$ expdp pp/gelc123 tables=test0 dumpfile=TEMP_DIR:test120100826.dmp logfile=TEMP_DIR:test120100826.log

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 07 December, 2012 19:42:48

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "PP"."SYS_EXPORT_TABLE_01":  pp/******** tables=test0 dumpfile=TEMP_DIR:test120100826.dmp logfile=TEMP_DIR:test120100826.log
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/REF_CONSTRAINT
. . exported "PP"."TEST0"                                43.98 KB      41 rows
Master table "PP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PP.SYS_EXPORT_TABLE_01 is:
  /devrman/expdp/test120100826.dmp

然后进行导入

1、在未导入主表的情况下

[oracle@b000-vmpomstestdb expdp]$ impdp pptest/gelc123  dumpfile=TEMP_DIR:test120100826.dmp logfile=TEMP_DIR:test120100826.log remap_schema=pp:pptest;

Import: Release 10.2.0.1.0 - 64bit Production on Friday, 07 December, 2012 19:43:06

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "PPTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PPTEST"."SYS_IMPORT_FULL_01":  pptest/******** dumpfile=TEMP_DIR:test120100826.dmp logfile=TEMP_DIR:test120100826.log remap_schema=pp:pptest
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PPTEST"."TEST0"                            43.98 KB      41 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "PPTEST"."TEST0" ADD CONSTRAINT "FK_TEST" FOREIGN KEY ("USER#") REFERENCES "PPTEST"."TEST" ("USER_ID") ENABLE
 
Job "PPTEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 19:43:09

2、在导入主表的情况下

[oracle@b000-vmpomstestdb expdp]$ impdp pptest/gelc123  dumpfile=TEMP_DIR:test120100826.dmp logfile=TEMP_DIR:test120100826.log remap_schema=pp:pptest;

Import: Release 10.2.0.1.0 - 64bit Production on Friday, 07 December, 2012 19:57:15

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "PPTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PPTEST"."SYS_IMPORT_FULL_01":  pptest/******** dumpfile=TEMP_DIR:test120100826.dmp logfile=TEMP_DIR:test120100826.log remap_schema=pp:pptest
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PPTEST"."TEST0"                            43.98 KB      41 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "PPTEST"."SYS_IMPORT_FULL_01" successfully completed at 19:57:19

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-671894/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-671894/