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