[20151125]IMPDP参数TABLE_EXISTS_ACTION

[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

--所以讲在工作中要注意！！！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1846224/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1846224/