ITPub博客

首页 > 数据库 > Oracle > Impdp之table_exists_action参数说明与用法

Impdp之table_exists_action参数说明与用法

原创 Oracle 作者:yhdmy 时间:2015-07-15 00:30:27 0 删除 编辑
一、官方文档
TABLE_EXISTS_ACTION
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL936

二、 适用场景
导入表时,表已存在,根据参数来选择处理方法。

三、参数选项
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
1. SKIP: 直接跳过,进行下一个对象操作。(默认)
2. APPEND: 在原有数据基础上继续追加
3. TRUNCATE: 先TRUNCATE,再完成数据插入
4. REPLACE: 先DROP表,然后创建表,最后完成数据插入

四、实验
1. 准备环境
(1) 用原有的SCOTT用户下的EMP表
scott@ORCL>select count(*) from emp;

  COUNT(*)
----------
        14

(2) 创建directory导出目录并赋权
sys@ORCL>create directory dump_dir as '/home/oracle/test';

Directory created.

sys@ORCL>grant write,read on directory dump_dir to public;

(3) 建立操作系统目录
[oracle@test ~]$ pwd
/home/oracle
[oracle@test ~]$ mkdir test 


2. EXPDP导出emp表数据,加入两条数据,再次导出。
(1)导出源表数据
[oracle@test ~]$ expdp scott/tiger directory=dump_dir tables=emp  dumpfile=expdat.dmp 

Export: Release 11.2.0.1.0 - Production on Tue Jul 14 15:36:27 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_dir tables=emp 
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
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/test/expdat.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:36:37

(2) 加两条数据
INSERT INTO "SCOTT"."EMP" (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) VALUES ('2', 'a2', 'dba', '7566', '6000', '20’);
INSERT INTO "SCOTT"."EMP" (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) VALUES ('1', 'a1', 'dba', '7566', '5000', '20’);
commit;

(3)再次导出数据
[oracle@test test]$ expdp scott/tiger directory=dump_dir tables=emp  dumpfile=expdat1.dmp

Export: Release 11.2.0.1.0 - Production on Tue Jul 14 16:03:20 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_dir tables=emp dumpfile=expdat1.dmp 
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
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.625 KB      16 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/test/expdat1.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:03:30

3. IMPDP导入emp表数据(使用table_exists_action四种参数)
(1)默认不加参数为skip
[oracle@test ~]$ impdp scott/tiger directory=dump_dir dumpfile=expdat.dmp

Import: Release 11.2.0.1.0 - Production on Tue Jul 14 15:40:53 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=dump_dir dumpfile=expdat.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 15:40:56

scott@ORCL>select count(*) from emp;

  COUNT(*)
----------
        14
 
(2)参数为 truncate,先truncate再加载数据
[oracle@test ~]$ impdp scott/tiger directory=dump_dir dumpfile=expdat.dmp table_exists_action=truncate

Import: Release 11.2.0.1.0 - Production on Tue Jul 14 15:58:36 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=dump_dir dumpfile=expdat.dmp table_exists_action=truncate 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "SCOTT"."EMP" 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"."EMP"                               8.570 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 15:58:40

scott@ORCL>select count(*) from emp;

  COUNT(*)
----------
        14

(3) 参数为replace,先DROP表,然后创建表,最后完成数据插入
[oracle@test ~]$ impdp scott/tiger directory=dump_dir dumpfile=expdat.dmp table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Tue Jul 14 16:01:27 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=dump_dir dumpfile=expdat.dmp table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               8.570 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 16:01:30

scott@ORCL>select count(*) from emp;

  COUNT(*)
----------
        14

(4) 参数为append,在原有数据基础上继续追加
A. 表中有主键的唯一索引导致报错,未成功导入数据。
[oracle@test test]$ impdp scott/tiger directory=dump_dir dumpfile=expdat1.dmp table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Tue Jul 14 16:05:39 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=dump_dir dumpfile=expdat1.dmp table_exists_action=append 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SCOTT"."EMP" 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
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 16:05:43

scott@ORCL>select count(*) from emp;

  COUNT(*)
----------
        14


B. 将表中主键失效,主键自带索引DROP掉,再次导入成功,数据追加了16条记录,总共30条记录。
[oracle@test test]$ impdp scott/tiger directory=dump_dir dumpfile=expdat1.dmp table_exists_action=append


Import: Release 11.2.0.1.0 - Production on Wed Jul 15 16:10:02 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=dump_dir dumpfile=expdat1.dmp table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SCOTT"."EMP" 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"."EMP"                               8.625 KB      16 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 16:10:04
 
scott@ORCL> select count(*) from emp;

  COUNT(*)
----------
        30


五、总结:该参数是用于IMPDP时,表已经存在,而且已有数据,因为用了该参数的话,可以直接将表进行处理后再insert,所以请考虑确认好源表数据再做处理。再有追加数据时,不能有唯一索引,否则无法加载数据成功,但这样会有冗余数据,后期可以对冗余数据再做处理。



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

请登录后发表评论 登录
全部评论
曾就职于铁道科学研究院,太极计算机公司,合力中税。负责过国家电网数据库部署、迁移、升级、灾备等实施规划工作。大唐发电厂、北京市公安局数据库运维及优化工作。现任金融工场高级DBA岗位,负责oracle、mysql数据库相关工作。拥有Oracle技术10g/11g的 OCP与OCM认证,Oracle YEP成员,OCM联盟成员。

注册时间:2013-04-22

  • 博文量
    106
  • 访问量
    1574238