1. 首页
  2. IT资讯

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://www.cxybcw.com/183187.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code