ORA-00001唯一约束违反：分布式PL/SQL存储过程执行报错求助

算力阿峰

2026-6-16

问题

编写了如下PL/SQL存储过程，用于通过RBM_DBLINK向远程数据库的PRODUCTATTRIBUTE表插入和更新数据：

declare
  datafix_id$c varchar2(100) := 'BSS-39038';
  datafix_version$c varchar2(100) := '1.0';
  description$c varchar(100) := 'BSS-39038 [CBM] Добавить ERT_SERVICE_ID на продукты облачных сервисов';
  cnt$i integer;
begin
  select count(1)
  into cnt$i
  from ert_datafixes_log edl1
  where edl1.datafix_id = datafix_id$c
    and edl1.datafix_version = datafix_version$c
    and edl1.result = 'APPLIED'
    and not exists(select 1
                   from ert_datafixes_log edl2
                   where edl2.datafix_id = edl1.datafix_id
                     and edl2.datafix_version = edl1.datafix_version
                     and edl2.result = 'ROLLEDBACK'
                     and edl2.execution_date > edl1.execution_date
                  );
 
  if cnt$i = 0 then
    insert into ert_datafixes_log(datafix_id, datafix_version, execution_date, result)
    values(datafix_id$c, datafix_version$c, current_date, 'APPLIED');
   
    insert into TMP_BSS39038_PRODUCTATTRIBUTE1 
    select distinct *
    from PRODUCTATTRIBUTE@rbm_dblink p
    where p.PRODUCT_ID in (8608833, 8608674, 8608675, 8608676, 8608677, 8608678, 8608679, 8608680,8608681,8608682,8608818,8608819,8608820,8608821,8608822,8608823,8608824,8608825,8608826,8608827,8608828,8608829,8608830,8608831,8608832);
     
    insert into TMP_BSS39038_PRODUCTATTRIBUTE2 
    select *
    from PRODUCTATTRIBUTE@rbm_dblink p
    where p.PRODUCT_ID in (8608246, 8608248, 8608249, 8608250, 8608251, 8608252, 8608253, 8608254, 8608255, 8608256, 8608257, 8608258, 8608259, 8608260, 8608261, 8608262, 8608263, 8608264, 8608265, 8608266, 8608267, 8608268)
    and p.PRODUCT_ATTRIBUTE_SUBID = 6;
     
    for rec1 in (select product_id
                from TMP_BSS39038_PRODUCTATTRIBUTE1
               )
      loop
        insert into PRODUCTATTRIBUTE@rbm_dblink (PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID, ATTRIBUTE_UA_NAME, ATTRIBUTE_BILL_NAME, 
        ATTRIBUTE_CLASS, MANDATORY_BOO, BREAKOUT_OBJECT, BREAKOUT_GET_BUTTON_BOO, DISPLAY_POSITION, PROV_ATTR_NUM, ATTRIBUTE_UNITS)
        values (rec1.PRODUCT_ID, 6, 'ERT_SERVICE_ID', 'ERT_SERVICE_ID', 1, 'F', '', 'F', 6, '', 'TX');
       
      end loop;
      
    for rec2 in (select PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID
                from TMP_BSS39038_PRODUCTATTRIBUTE2
               )
      loop
        update PRODUCTATTRIBUTE@rbm_dblink 
        set DISPLAY_POSITION=6, ATTRIBUTE_UA_NAME='ERT_SERVICE_ID', ATTRIBUTE_BILL_NAME='ERT_SERVICE_ID'
        where PRODUCT_ID = rec2.product_id and PRODUCT_ATTRIBUTE_SUBID = rec2.PRODUCT_ATTRIBUTE_SUBID;
       
      end loop;
 
    commit;
    dbms_output.put_line('Датафикс применен успешно');
  else
    dbms_output.put_line('Датафикс уже был применен');
  end if;
end;

执行该存储过程时出现如下错误：

SQL Error [2055] [42000]: ORA-02055: 分布式更新操作失败；需要回滚
ORA-00001: 违反唯一约束条件 (GENEVA_ADMIN.PRODUCTACTRIBUTE_PK)
ORA-02063: 来自 RBM_DBLINK 的前一行
ORA-06512: 在第45行
ORA-06512: 在第45行

确认是插入操作导致错误，但单独针对特定PRODUCT_ID（如8608833）执行插入操作可成功：

insert into PRODUCTATTRIBUTE@rbm_dblink (PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID, ATTRIBUTE_UA_NAME,
        ATTRIBUTE_BILL_NAME, ATTRIBUTE_CLASS, MANDATORY_BOO, BREAKOUT_OBJECT, BREAKOUT_GET_BUTTON_BOO, DISPLAY_POSITION, PROV_ATTR_NUM, ATTRIBUTE_UNITS)
        values (8608833, 6, 'ERT_SERVICE_ID', 'ERT_SERVICE_ID', 1, 'F', '', 'F', 6, '', 'TX')

PRODUCTATTRIBUTE_PK约束由PRODUCT_ID（NUMBER(9,0)）和PRODUCT_ATTRIBUTE_SUBID（NUMBER(9,0)）组成，需排查报错原因。

排查分析与解决方案

核心原因

临时表数据重复：TMP_BSS39038_PRODUCTATTRIBUTE1中存在重复的PRODUCT_ID。插入临时表时用了distinct *，但如果PRODUCTATTRIBUTE表中同一PRODUCT_ID对应多条不同属性记录，distinct *会保留这些不同记录，导致临时表中同一PRODUCT_ID出现多次。后续循环插入时，会对同一个PRODUCT_ID重复插入PRODUCT_ATTRIBUTE_SUBID=6的记录，触发唯一约束。
分布式事务特性：单独执行单条插入时，事务仅包含一条远程操作；而存储过程中是批量循环插入，属于分布式事务，一旦某条插入违反约束，整个分布式事务会触发回滚，抛出ORA-02055错误。

修复步骤

修改临时表插入逻辑：确保TMP_BSS39038_PRODUCTATTRIBUTE1中仅保留唯一的PRODUCT_ID，将插入临时表的SQL改为：

insert into TMP_BSS39038_PRODUCTATTRIBUTE1 
select distinct p.PRODUCT_ID
from PRODUCTATTRIBUTE@rbm_dblink p
where p.PRODUCT_ID in (8608833, 8608674, 8608675, 8608676, 8608677, 8608678, 8608679, 8608680,8608681,8608682,8608818,8608819,8608820,8608821,8608822,8608823,8608824,8608825,8608826,8608827,8608828,8608829,8608830,8608831,8608832);

注：如果临时表结构仅需要PRODUCT_ID，需先调整临时表结构；若必须保留全字段，可改用group by PRODUCT_ID来确保每个PRODUCT_ID只出现一次。

添加存在性检查：在循环插入前，先检查目标表中是否已存在该PRODUCT_ID和PRODUCT_ATTRIBUTE_SUBID=6的记录，避免重复插入：

for rec1 in (select product_id
            from TMP_BSS39038_PRODUCTATTRIBUTE1
           )
  loop
    if not exists(select 1 from PRODUCTATTRIBUTE@rbm_dblink 
                  where PRODUCT_ID = rec1.PRODUCT_ID and PRODUCT_ATTRIBUTE_SUBID = 6) then
      insert into PRODUCTATTRIBUTE@rbm_dblink (PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID, ATTRIBUTE_UA_NAME, ATTRIBUTE_BILL_NAME, 
      ATTRIBUTE_CLASS, MANDATORY_BOO, BREAKOUT_OBJECT, BREAKOUT_GET_BUTTON_BOO, DISPLAY_POSITION, PROV_ATTR_NUM, ATTRIBUTE_UNITS)
      values (rec1.PRODUCT_ID, 6, 'ERT_SERVICE_ID', 'ERT_SERVICE_ID', 1, 'F', '', 'F', 6, '', 'TX');
    end if;
  end loop;

优化分布式事务：考虑将批量插入改为单条远程批量插入（如使用insert ... select），减少分布式事务的开销和出错概率：

insert into PRODUCTATTRIBUTE@rbm_dblink (PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID, ATTRIBUTE_UA_NAME, ATTRIBUTE_BILL_NAME, 
ATTRIBUTE_CLASS, MANDATORY_BOO, BREAKOUT_OBJECT, BREAKOUT_GET_BUTTON_BOO, DISPLAY_POSITION, PROV_ATTR_NUM, ATTRIBUTE_UNITS)
select distinct p.PRODUCT_ID, 6, 'ERT_SERVICE_ID', 'ERT_SERVICE_ID', 1, 'F', '', 'F', 6, '', 'TX'
from PRODUCTATTRIBUTE@rbm_dblink p
where p.PRODUCT_ID in (8608833, 8608674, 8608675, 8608676, 8608677, 8608678, 8608679, 8608680,8608681,8608682,8608818,8608819,8608820,8608821,8608822,8608823,8608824,8608825,8608826,8608827,8608828,8608829,8608830,8608831,8608832)
and not exists(select 1 from PRODUCTATTRIBUTE@rbm_dblink 
               where PRODUCT_ID = p.PRODUCT_ID and PRODUCT_ATTRIBUTE_SUBID = 6);

内容的提问来源于stack exchange，提问作者Nadine241197

ORA-00001唯一约束违反：分布式PL/SQL存储过程执行报错求助

问题 #

排查分析与解决方案 #

核心原因 #

修复步骤 #

问题

排查分析与解决方案

核心原因

修复步骤