Impdp之table_exists_action参数说明与⽤法⼀、官⽅⽂档
TABLE_EXISTS_ACTION
⼆、适⽤场景
导⼊表时，表已存在，根据参数来选择处理⽅法。
三、参数选项
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
1. SKIP: 直接跳过，进⾏下⼀个对象操作。（默认）
2. APPEND: 在原有数据基础上继续追加
3. TRUNCATE: 先TRUNCATE，再完成数据插⼊
4. REPLACE: 先DROP表，然后创建表，最后完成数据插⼊
四、实验
1. 准备环境exists的用法
(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
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
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，所以请考虑确认好源表数据再做处理。再有追加数据时，不能有唯⼀索引，否则⽆法加载数据成功，但这样会有冗余数据，后期可以对冗余数据再做处理。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论