ITPub博客

首页 > 数据库 > Oracle > impdp导入报ORA-00001 ORA-04088错误

impdp导入报ORA-00001 ORA-04088错误

原创 Oracle 作者:xueshancheng 时间:2021-11-26 09:36:11 0 删除 编辑

1 使用impdp导入数据,报如下错误

ORA-31693: Table data object "USER_A"."SYNC_TABLE_A" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-00001: unique constraint (USER_A.P_USER_PK) violated

ORA-06512: at "USER_A.SYNC_TABLE_A_INSERT", line 3

ORA-04088: error during execution of trigger 'USER_A.SYNC_TABLE_A_INSERT'


2   在原始库查询是否有重复数据,发现没有冗余数据

SYS@sourcedb1 >select CN,count(*) from USER_A.SYNC_TABLE_A having count(*) >1 group by CN;


no rows selected


3 检查触发器的状态及内容

SYS@targetdb1 >select OWNER,TRIGGER_NAME,STATUS from dba_triggers where TRIGGER_NAME='SYNC_TABLE_A_INSERT';


OWNER                          TRIGGER_NAME                   STATUS

------------------------------ ------------------------------ --------

USER_A                         SYNC_TABLE_A_INSERT        ENABLED


查看触发器的内容,发现将此表的数据插入到另一个表中

set linesize 300

set pagesize 999

set long 999999

SELECT DBMS_METADATA.GET_DDL('TRIGGER','SYNC_TABLE_A_INSERT','USER_A') from dual;



 CREATE OR REPLACE TRIGGER "USER_A"."SYNC_TABLE_A_INSERT"

  after insert or update or delete ON "SYNC_TABLE_A"  FOR EACH ROW

BEGIN

  if inserting then

  INSERT INTO PORTAL_USER_B t

    (id,

     login_name,

     full_name,

.。。。。


查看另一个表是否有数据,发现有 

SYS@targetdb1 >select count(*) from USER_A.PORTAL_USER_B;


  COUNT(*)

----------

     61931

另外就是已经在此表中将数据进行了导入。 

. . imported "USER_A"."PORTAL_USER_B"          4.001 MB   61931 rows


5  解决方法

 禁用触发器。

 SYS@targetdb1 > alter trigger USER_A.SYNC_TABLE_A_INSERT disable;


Trigger altered.


SYS@targetdb1 >select OWNER,TRIGGER_NAME,STATUS from dba_triggers where TRIGGER_NAME='SYNC_TABLE_A_INSERT';


OWNER                          TRIGGER_NAME                   STATUS

------------------------------ ------------------------------ --------

USER_A                SYNC_TABLE_A_INSERT        DISABLED


6   禁用后,再次导入,没有任何问题。

 [oracle@targetdb1 ~]$ impdp \'/ as sysdba\'  JOB_NAME=xsc1123 directory=EXPDP   dumpfile=SYNC_NOVELL_1123.dmp logfile=SYNC_NOVELL_1123.log   TABLE_EXISTS_ACTION=TRUNCATE


Import: Release 11.2.0.4.0 - Production on Tue Nov 23 10:10:39 2021


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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SYS"."XSC1123" successfully loaded/unloaded

Starting "SYS"."XSC1123":  "/******** AS SYSDBA" JOB_NAME=xsc1123 directory=EXPDP dumpfile=SYNC_NOVELL_1123.dmp logfile=SYNC_NOVELL_1123.log TABLE_EXISTS_ACTION=TRUNCATE 

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "USER_A"."SYNC_TABLE_A"        1.702 MB   20444 rows

Job "SYS"."XSC1123" successfully completed at Tue Nov 23 10:10:40 2021 elapsed 0 00:00:01


7 启用触发器,避免因为触发器禁用导致的数据问题

SYS@targetdb1 >alter trigger USER_A.SYNC_TABLE_A_INSERT enable;


Trigger altered.


SYS@targetdb1 >select OWNER,TRIGGER_NAME,STATUS from dba_triggers where TRIGGER_NAME='SYNC_TABLE_A_INSERT';


OWNER                          TRIGGER_NAME                   STATUS

------------------------------ ------------------------------ --------

USER_A                     SYNC_TABLE_A_INSERT                ENABLED


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

请登录后发表评论 登录
全部评论
本人目前就职于北京海天起点技术服务有限股份公司,从事Oracle数据库有十几年了,对Oracle及goldengate比较精通。

注册时间:2021-03-11

  • 博文量
    44
  • 访问量
    13123