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

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

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