加入收藏 | 设为首页 |

【IMPDP】当导入数据时遭遇表已存时的应对手段

内地新闻 时间:2020-04-02 编辑:申博sunbet 浏览:
当使用IMPDP完成数据库导入时,如遇到表已存在时,Oracle提供给我们如下四种处理方式: a.忽略(SKIP,默认行为); b.在原有数据基础上继续增加(APPEND); c.先DROP表,然后创建表,最后完成数据插入(REPLACE); d.先TRUNCATE,再完成数据插入(TRUNC

当使用IMPDP完成数据库导入时,如遇到表已存在时,Oracle提供给我们如下四种处理方式:
a.忽略(SKIP,默认行为);
b.在原有数据基础上继续增加(APPEND);
c.先DROP表,然后创建表,最后完成数据插入(REPLACE);
d.先TRUNCATE,再完成数据插入(TRUNCATE)。

真实感受一下这几种方式的效果,供参考。

1.IMPDP在线帮助中关于TABLE_EXISTS_ACTION参数的描述
TABLE_EXISTS_ACTION   Action to take if imported object already exists.
                      Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.

2.Oracle官方文档中的描述
#sthref365
Oracle官方文档是根本,需研读。

3.使用EXPDP生成一份dump文件
sec@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=`date +"%Y%m%d%H%M%S"`_sec.dmp logfile=`date +"%Y%m%d%H%M%S"`_sec.log

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:29:17

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01":  sec/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "SEC"."T"                                   2.259 MB   24360 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
  /expdp/20100401102917_sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:20

4.分别使用四中方式真实的感知一下具体效果
用到的IMPDP语句统一汇总在这里,方便参考。
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=SKIP
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=APPEND
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=REPLACE
impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=TRUNCATE

1)默认行为SKIP
sec@ora10g> select count(*) from t;

  COUNT(*)
----------
     24360

sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:50:41

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "SEC"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:50:42

上面的行为与下面一条显示的指定“SKIP”效果是一样的,不赘述。
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=SKIP

2)APPEND方式
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=APPEND

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:51:32

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=APPEND
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "SEC"."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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T"                                   2.259 MB   24360 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:51:33

此时表T的记录数翻倍。
sec@ora10g> select count(*) from t;

  COUNT(*)
----------
     48720

3)REPLACE方式
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=REPLACE

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:54:28

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=REPLACE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T"                                   2.259 MB   24360 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 10:54:29

sec@ora10g> select count(*) from t;

  COUNT(*)
----------
     24360

4)TRUNCATE方式
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=TRUNCATE

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 April, 2010 10:54:55

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=TRUNCATE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39153: Table "SEC"."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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T"                                   2.259 MB   24360 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:54:56

sec@ora10g> select count(*) from t;

  COUNT(*)
----------
     24360

5.小结
IMPDP的TABLE_EXISTS_ACTION参数相比IMP工具是一个进步,我们的控制手段得到加强,莫要固步自封。

Good luck.

secooler
10.04.06

-- The End --

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

港媒曝刘璇订婚 经纪人回应:小道消息不予评论

港媒曝刘璇订婚 经纪人回应:小道消息不予评论

港媒曝刘璇与相恋八年的男友订婚。(资料图) 近日有香港杂志爆...[详细]

余少群否认遭古天乐排挤:过节会发短信问候

余少群否认遭古天乐排挤:过节会发短信问候

余少群否认与古天乐不和 余少群直言不知被“封杀” 红网3月29...[详细]