impdp匯入報ORA-00001 ORA-04088錯誤

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,

.。。。。

4  檢視另一個表是否有資料，發現有 

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