impdp table_exists_action参数

最新推荐文章于 2023-12-19 17:43:11 发布
最新推荐文章于 2023-12-19 17:43:11 发布
阅读量1k 收藏 5
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013737399/article/details/52801159
版权
impdp table_exists_action参数

1.当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式：
a.忽略（SKIP，默认行为） 注意；无论表中的数据是否与要导入的表的数据量是否一致，都会skip。
b.在原有数据基础上继续增加（APPEND）
c.先DROP表，然后创建表，最后完成数据插入（REPLACE）
d.先TRUNCATE，再完成数据插入（TRUNCATE）。

2.使用EXPDP生成两份dump文件，一份导出scott下emp表的所有记录，另外一份导出scott下emp表中sal>3500的数据，此时只有一条数据。
导出命令如下：
expdp system/oracle directory=d1 dumpfile=`date +"%Y%m%d%H%M%S"`_scott_t1_full.dmp schemas=scott include=table:\"=\'T1\'\"  logfile=`date +"%Y%m%d%H%M%S"`_scott_t1.log
expdp system/oracle directory=d1 dumpfile=`date +"%Y%m%d%H%M%S"`_scott_t1_sal.dmp schemas=scott include=table:\"=\'T1\'\" query=t1:'"where sal > 3500"' logfile=`date +"%Y%m%d%H%M%S"`_scott_t1.log

2.1.导出scott下emp表中所有的数据
[oracle@test01 ~]$ expdp system/oracle directory=d1 dumpfile=`date +"%Y%m%d%H%M%S"`_scott_t1_full.dmp schemas=scott include=table:\"=\'T1\'\"  logfile=`date +"%Y%m%d%H%M%S"`_scott_t1.log

Export: Release 11.2.0.4.0 - Production on Wed Oct 12 19:11:04 2016

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=d1 dumpfile=20161012191104_scott_t1_full.dmp schemas=scott include=table:"='T1'" logfile=20161012191104_scott_t1.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."T1"                                8.570 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/20161012191104_scott_t1_full.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 19:11:30 2016 elapsed 0 00:00:20


2.2.导出scott下emp表中sal>3500的数据
[oracle@test01 ~]$ expdp system/oracle directory=d1 dumpfile=`date +"%Y%m%d%H%M%S"`_scott_t1_sal.dmp schemas=scott include=table:\"=\'T1\'\" query=t1:'"where sal > 3500"' logfile=`date +"%Y%m%d%H%M%S"`_scott_t1.log

Export: Release 11.2.0.4.0 - Production on Wed Oct 12 19:13:22 2016

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=d1 dumpfile=20161012191322_scott_t1_sal.dmp schemas=scott include=table:"='T1'" query=t1:"where sal > 3500" logfile=20161012191322_scott_t1.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."T1"                                8.039 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/20161012191322_scott_t1_sal.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 19:13:40 2016 elapsed 0 00:00:15

导出的两份dmp文件如下
[oracle@test01 ~]$ ls -lrt
total 368
-rw-r-----. 1 oracle oinstall 184320 Oct 12 19:11 20161012191104_scott_t1_full.dmp
-rw-r-----. 1 oracle oinstall 184320 Oct 12 19:13 20161012191322_scott_t1_sal.dmp

3.使用四种不同的参数，对比不同参数的不同结果。
sqlpus / as sysdba
conn kang/oracle
SQL> select count(*) from t1 ;

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

SQL> select * from t1 where sal>3500 ;

     EMPNO ENAME      JOB       MGR HIREDATE    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT   17-NOV-81   5000   10

SQL> delete from t1 where sal>3500 ;

1 row deleted.

SQL> commit ;

Commit complete.


SQL> select * from t1 where sal>3500 ;

no rows selected

SQL> select count(*) from t1 ;

  COUNT(*)
----------
13

impdp system/oracle directory=d1 dumpfile=20161012191322_scott_t1_sal.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang 
等同于
impdp system/oracle directory=d1 dumpfile=20161012191322_scott_t1_sal.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=SKIP


导入过程如下：
[oracle@test01 ~]$ impdp system/oracle directory=d1 dumpfile=20161012191322_scott_t1_sal.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang

Import: Release 11.2.0.4.0 - Production on Wed Oct 12 19:49:44 2016

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=d1 dumpfile=20161012191322_scott_t1_sal.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "KANG"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Oct 12 19:49:49 2016 elapsed 0 00:00:03

SQL> select count(*) from t1 ;

  COUNT(*)
----------
13

结论：当参数table_exists_action=skip  即为忽略时(SKIP，默认行为),无论表中的数据是否与要导入的表的数据量是否一致，都会skip。

使用TABLE_EXISTS_ACTION的四种参数进行测试，测试结果如下

impdp system/oracle directory=d1 dumpfile=20161012191322_scott_t1_sal.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=SKIP
impdp system/oracle directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=APPEND
impdp system/oracle directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=REPLACE
impdp system/oracle directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=TRUNCATE

1）默认行为SKIP
SQL> select count(*) from t1 ;

  COUNT(*)
----------
13

导入数据（此时导入的数据只有一天记录就是sal>3500的那条数据）
[oracle@test01 ~]$ impdp system/oracle directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang

Import: Release 11.2.0.4.0 - Production on Wed Oct 12 19:24:51 2016

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "KANG"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Oct 12 19:24:56 2016 elapsed 0 00:00:03

上面的行为与下面一条显示的指定“SKIP”效果是一样的，不赘述。
sec@secDB /expdp$ impdp system/sys directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log TABLE_EXISTS_ACTION=SKIP

SQL> select count(*) from t1 ;

  COUNT(*)
----------
13

2）APPEND方式
[oracle@test01 ~]$ impdp system/oracle directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=APPEND

Import: Release 11.2.0.4.0 - Production on Wed Oct 12 19:26:42 2016

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=APPEND 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "KANG"."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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "KANG"."T1"                                 8.570 KB      14 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Oct 12 19:26:51 2016 elapsed 0 00:00:07

此时表T的记录数出入了14条记录，此时一共会有27条数据。
SQL> select count(*) from t1 ;

  COUNT(*)
----------
27

3）REPLACE方式
[oracle@test01 ~]$ impdp system/oracle directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=REPLACE

Import: Release 11.2.0.4.0 - Production on Wed Oct 12 19:28:38 2016

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=REPLACE 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "KANG"."T1"                                 8.570 KB      14 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Oct 12 19:28:44 2016 elapsed 0 00:00:05

SQL> select count(*) from t1 ;

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

4）TRUNCATE方式
[oracle@test01 ~]$ impdp system/oracle directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=TRUNCATE

Import: Release 11.2.0.4.0 - Production on Wed Oct 12 19:29:29 2016

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=d1 dumpfile=20161012191104_scott_t1_full.dmp logfile=20100401102917_scott_impdp.log remap_schema=scott:kang TABLE_EXISTS_ACTION=TRUNCATE 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "KANG"."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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "KANG"."T1"                                 8.570 KB      14 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Oct 12 19:29:37 2016 elapsed 0 00:00:06

SQL> select count(*) from t1 ;


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

ksf泡面
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle imp table_exists_action,Impdp之table_exists_action参数说明与用法
weixin_35773740的博客
04-14 2539
一、官方文档TABLE_EXISTS_ACTION二、 适用场景导入表时，表已存在，根据参数来选择处理方法。三、参数选项TABLE_EXISTS_ACTION=[SKIP|APPEND|TRUNCATE|REPLACE]1. SKIP: 直接跳过，进行下一个对象操作。(默认)2. APPEND:在原有数据基础上继续追加3. TRUNCATE:先TRUNCATE，再完成数据插入4. ...
oracle expdp-impdp_用法详解
05-31
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append; ``` #### EXPDP命令行选项解析 - **ATTACH**：用于建立客户端会话与现有导出作业之间的联系。示例： ...
oracle impdp的table_exists_action详解
热门推荐
清风小阁
01-06 11万+
1 table_exists_action参数说明 使用imp进行数据导入时，若表已经存在，要先drop掉表，再进行导入。 而使用impdp完成数据库导入时，若表已经存在，有四种的处理方式： 1)  skip：默认操作 2)  replace：先drop表，然后创建表，最后插入数据 3)  append：在原来数据的基础上增加数据 4)  truncate：先truncate，然后再插
【IMPDP】当导入数据时遭遇表已存时的应对手段——TABLE_EXISTS_ACTION参数
皓月如我的专栏
03-07 2万+
[转自：http://space.itpub.net/519536/viewspace-631445] 当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式： a.忽略（SKIP，默认行为）； b.在原有数据基础上继续增加（APPEND）； c.先DROP表，然后创建表，最后完成数据插入（REPLACE）； d.先TRUNCATE，再完成数据插入（T
expdp-impdp_用法详解
06-05
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append; 该命令将 expdp.dmp 文件中的数据追加到 system 用户下的对应表中。 EXPDP 命令行选项 EXPDP 命令支持...
oracle_impdp
06-01
4. **表已存在**：当遇到表已存在的情况时，可以根据实际需求选择不同的处理方式，例如使用`table_exists_action=replace`来替换原有表。 #### 七、总结 通过上述介绍，我们可以看到`impdp`是一个非常强大的工具，...
expdb/impdp使用方法
最新发布
01-29
impdp user/passwd directory=DP_DIR dumpfile=expdp.dmp logfile=impdp.log table_exists_action=APPEND ``` **逻辑备份恢复迁移的场景与优势** 使用expdp/impdp进行逻辑备份恢复迁移的主要场景包括数据库迁移、...
expdp—impdp
04-12
3. 对于增量更新，可以结合“METADATA_ONLY”选项和“TABLE_EXISTS_ACTION”参数使用。 4. 为避免冲突，导出和导入前应做好充分的计划，包括目标数据库的清理和设置。 总结，expdp和impdp是Oracle数据库管理的重要...
impdp分区表约束违反后导致部分数据导入table_exists_action=truncate
jnrjian的博客
12-19 114
分区表只会truncate 一次，但是每个分区独立导入，单个分区的数据整体导入。不同分区的数据可以分别导入。
IMPDP参数TABLE_EXISTS_ACTION
cilonggang7850的博客
12-03 193
当IMPDP导入数据时遭遇表已存时的应对手段——TABLE_EXISTS_ACTION参数 当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式： a.忽略（SKIP，...
Impdp之table_exists_action参数说明与用法
civbl2961的博客
07-15 722
一、官方文档 TABLE_EXISTS_ACTION http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTI...
oracle10g支持数据泵么,Oracle 10g使用数据泵(EXPDP和IMPDP)。
weixin_35583709的博客
04-11 355
1.1.1 expdp使用使用EXPDP工具时,其转储文件只能被存放在DIRECTORY对象对应的OS目录中,而不能直接指定转储文件所在的OS目录.因此使用EXPDP工具时,必须首先建立DIRECTORY对象.并且需要为数据库用户授予使用DIRECTORY对象权限.首先得建DIRECTORY:SQL> conn /as sysdbaSQL> CREATE OR REPLACE DIRE...
table_exists_action=append和table_exists_action=truncate
weixin_43475880的博客
05-23 1668
table_exists_action=append和table_exists_action=truncate 一、环境准备 1.1 192.168.1.22上创建single01.student表 SQL> select * from single01.student; ID NAME ------------ -------------------------------------------------------------------- ...
IMPDP table_exists_action 参数的应用
在实施道路上越走越远的老鸟程序猿
12-18 1614
当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式： a.忽略（SKIP，默认行为）； b.在原有数据基础上继续增加（APPEND）； c.先DROP表，然后创建表，最后完成数据插入（REPLACE）； d.先TRUNCATE，再完成数据插入（TRUNCATE）。 下面做实验来看看几种方式的效果： ========================
Oracle17学习总结——数据泵EXPDP和IMPDP
weixin_38400733的博客
04-07 362
（1）
oracle impdb table,Oracle中impdp的TABLE_EXISTS_ACTION参数
weixin_39600447的博客
04-08 143
TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}The possible values have the following effects:SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CO...
impdp table_exists_action
04-11
impdp的table_exists_action参数用于指定在导入数据时发现表已经存在时的处理方式。可以设置为：skip（跳过）、append（追加）、replace（替换）、truncate（截断再插入）。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值