[20151125]数据泵IMPDP参数TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE.txt
--当使用impdp导入参数时，如果导入的表信息已经存在可以使用TABLE_EXISTS_ACTION控制导入行为，自己做一个测试：
--注意一些参数可能会破坏原来数据库对应信息，在操作时特别注意理解这些参数的含义：
$ impdp help=y
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.
SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in
conventional import utility.
APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table
and the existing data remains unchanged.
TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump
REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE
options are not valid if you set the CONTENT=DATA_ONLY for the impdp.
-- [SKIP] 是缺省参数，表示如果存在跳过。
-- APPEND 在原来基础上追加数据。
-- TRUNCATE 是先truncate表然后在导入。
-- REPLACE 是先drop，在建立新的表。
1.测试环境建立：
SCOTT@book> create table t1 as select rownum c1 from dual connect by level<=2;
Table created.
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select data_object_id,object_id,object_name from dba_objects where owner=user and object_name='T1';
DATA_OBJECT_ID OBJECT_ID OBJECT_NAME
-------------- ---------- --------------------
89366 89366 T1
$ expdp scott/book dumpfile=scott78.dmp logfile=scott78.log tables=t1
Export: Release 11.2.0.4.0 - Production on Wed Nov 25 11:35:06 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/a******* dumpfile=scott78.dmp logfile=scott78.log tables=t1
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
. . exported "SCOTT"."T1" 5.007 KB 2 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/book/dpdump/scott78.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 25 11:35:11 2015 elapsed 0 00:00:04
2.测试无参数TABLE_EXISTS_ACTION的情况：
$ impdp scott/book dumpfile=scott78.dmp logfile=scott78x.log tables=t1
Import: Release 11.2.0.4.0 - Production on Wed Nov 25 11:36:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a******* dumpfile=scott78.dmp logfile=scott78x.log tables=t1
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "SCOTT"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed Nov 25 11:36:18 2015 elapsed 0 00:00:02
--相当缺省的TABLE_EXISTS_ACTION=skip.
3.TABLE_EXISTS_ACTION=skip的情况：
$ impdp scott/book dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=skip
Import: Release 11.2.0.4.0 - Production on Wed Nov 25 11:37:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a******* dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Wed Nov 25 11:37:16 2015 elapsed 0 00:00:01
4.TABLE_EXISTS_ACTION=append的情况：
$ impdp scott/book dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=append
Import: Release 11.2.0.4.0 - Production on Wed Nov 25 11:38:04 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a****** dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T1" 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 TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T1" 5.007 KB 2 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Wed Nov 25 11:38:07 2015 elapsed 0 00:00:02
SCOTT@book> select data_object_id,object_id,object_name from dba_objects where owner=user and object_name='T1';
DATA_OBJECT_ID OBJECT_ID OBJECT_NAME
-------------- ---------- --------------------
89366 89366 T1
SCOTT@book> select * from t1;
C1
----------
1
2
1
2
--object_id,DATA_OBJECT_ID=89366,信息增加1倍。
5.TABLE_EXISTS_ACTION=replace的情况：
$ impdp scott/book dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=replace
Import: Release 11.2.0.4.0 - Production on Wed Nov 25 11:40:04 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a******* dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T1" 5.007 KB 2 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Wed Nov 25 11:40:06 2015 elapsed 0 00:00:01
SCOTT@book> select data_object_id,object_id,object_name from dba_objects where owner=user and object_name='T1';
DATA_OBJECT_ID OBJECT_ID OBJECT_NAME
-------------- ---------- --------------------
89479 89479 T1
SCOTT@book> select * from t1;
C1
----------
1
2
--object_id,DATA_OBJECT_ID已经发生了变化，说明是先drop在建立新表，再导入信息。
6.TABLE_EXISTS_ACTION=truncate的情况：
$ impdp scott/book dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=truncate
Import: Release 11.2.0.4.0 - Production on Wed Nov 25 11:41:37 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a******* dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T1" 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 TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T1" 5.007 KB 2 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Wed Nov 25 11:41:40 2015 elapsed 0 00:00:02
SCOTT@book> select data_object_id,object_id,object_name from dba_objects where owner=user and object_name='T1';
DATA_OBJECT_ID OBJECT_ID OBJECT_NAME
-------------- ---------- --------------------
89503 89479 T1
SCOTT@book> select * from t1;
C1
----------
1
2
--对比前面可以发现OBJECT_ID没有变化，而DATA_OBJECT_ID发生了变化，说明truncate表然后再导入数据。
7. 最后测试另外一个参数IGNORE=Y。
--注意在讲skip提到如下：
SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in
conventional import utility.
--如果你查询impdp 帮助，没有ignore=y这个参数，而实际上oracle为了帮助原来使用imp/exp的用户，依旧支持在impdp/expdp使用一些
--旧参数，它会做一些转换。
--但是要注意如果在impdp中使用ignore=y，不是表示TABLE_EXISTS_ACTION=skip的意思，而是append，通过测试来证明这一点。
$ impdp scott/book dumpfile=scott78.dmp logfile=scott78x.log tables=t1 ignore=y
Import: Release 11.2.0.4.0 - Production on Wed Nov 25 11:49:28 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/a****** dumpfile=scott78.dmp logfile=scott78x.log tables=t1 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T1" 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 TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T1" 5.007 KB 2 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Wed Nov 25 11:49:29 2015 elapsed 0 00:00:01
--注意看提示!!!
--也就是讲imp 使用ignore=y 相当于TABLE_EXISTS_ACTION=skip,而在impdp 使用ignore=y 相当于TABLE_EXISTS_ACTION=append。
SCOTT@book> select data_object_id,object_id,object_name from dba_objects where owner=user and object_name='T1';
DATA_OBJECT_ID OBJECT_ID OBJECT_NAME
-------------- ---------- --------------------
89503 89479 T1
SCOTT@book> select * from t1;
C1
----------
1
2
1
2
--所以讲在工作中要注意！！！
