[20151125]IMPDP参数TABLE_EXISTS_ACTION

简介: [20151125]数据泵IMPDP参数TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE.txt --当使用impdp导入参数时,如果导入的表信息已经存在可以使用TABLE_EXISTS_ACTIO...

[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

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

相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
14天前
|
存储 SQL 关系型数据库
CREATE TABLE语句
在MySQL中,使用CREATE TABLE语句来创建表。你需要指定表名和列的定义,包括列名、数据类型以及约束等,结合实际存储和上一课学习的数据类型选取合适的。创建一个book_types表
165 0
|
SQL 数据库
CREATE TABLE 语句
CREATE TABLE 语句
108 1
|
SQL 关系型数据库 Oracle
[20180211]current_schema与dblink.txt
[20180211]current_schema与dblink.txt --//有时候调优sql语句,经常在回话设在alter session set current_schema=scott,然后执行sql语句.
1089 0
|
Oracle 关系型数据库 数据库
|
SQL
sql中exists,not exists的用法
exists : 强调的是是否返回结果集,不要求知道返回什么, 比如:   select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要 exists引导的子句有结果集返回,那么exists这个条件就算成立了,大家注意返回的字段始终为1,如果改成“select 2 from grade where ...”,那么返回的字段就是2,这个数字没有意义。
1061 0