- 论坛徽章:
- 0
|
当使用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官方文档中的描述 http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#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工具是一个进步,我们的控制手段得到加强,莫要固步自封。
|
|