当前位置:  数据库>oracle

Oracle impdp的skip_constraint_errors选项跳过唯一约束错误

    来源: 互联网  发布时间:2017-06-22

    本文导语: 以前遇到impdp导入到已经存在的表有唯一索引,且要导入的数据跟现在数据有唯一冲突.一般处理方法是先把唯一索引删掉,导入后再删除重复数据再重建索引,或者把表导入为另一个表名,然后再进行关联导入.Oracle 11g开始impdp加了一...

以前遇到impdp导入到已经存在的表有唯一索引,且要导入的数据跟现在数据有唯一冲突.一般处理方法是先把唯一索引删掉,导入后再删除重复数据再重建索引,或者把表导入为另一个表名,然后再进行关联导入.Oracle 11g开始impdp加了一个data_ooptions参数,参数目前的选项只有skip_constraint_errors,用于在导入时忽略非延迟约束继续执行.

以下测试:
oracle version :11.2.0.4

[oracle@ct6605 ~]$ ORACLE_SID=ct66
[oracle@ct6605 ~]$ sqlplus / as sysdba

#建测试表
SQL> create table scott.t_source as select * from dba_objects;

 #建导入导出目录
SQL> create or replace directory home_dump as '/home/oracle';
SQL> exit

#导出测试数据
[oracle@ct6605 ~]$ expdp system dumpfile=home_dump:expdp_t_source.dmp tables=scott.t_source

Export: Release 11.2.0.4.0 - Production on Fri Mar 25 11:28:56 2016

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

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_TABLE_01":  system/******** dumpfile=home_dump:expdp_t_source.dmp tables=scott.t_source
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_SOURCE"                          8.395 MB  86527 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/expdp_t_source.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 25 11:29:03 2016 elapsed 0 00:00:04


[oracle@ct6605 ~]$ sqlplus / as sysdba
#建目标表
SQL> create table scott.t_dest as select * from dba_objects where rownum update scott.t_dest set object_id=object_id+2000000;
SQL> insert into scott.t_dest select * from dba_objects where rownum commit;
#建唯一索引
SQL> create unique index idx_t_dest on scott.t_dest(object_id);
SQL> exit

#impdp不加skip_constraint_errors时报错
[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append

Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:34:45 2016

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

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/******** dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T_DEST" 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"."T_DEST" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SYS.IDX_T_DEST) violated
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Mar 25 11:34:51 2016 elapsed 0 00:00:04

#impdp加skip_constraint_errors,并且导入日志中显示总共多少行,导入了多少行,多少行因为什么约束没导入
[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append data_options=skip_constraint_errors

Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:36:55 2016

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

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/******** dumpfile=home_dump:expdp_t_source.dmp remap_table=scott.t_source:t_dest table_exists_action=append data_options=skip_constraint_errors
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."T_DEST" 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"."T_DEST"                            8.395 MB  86518 out of 86527 rows
9 row(s) were rejected with the following error:
ORA-00001: unique constraint (SYS.IDX_T_DEST) violated

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Mar 25 11:37:05 2016 elapsed 0 00:00:07


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle deferrable选项
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3