oracle 10g merge into 报 ora-02064错误

时间：2010-10-19 来源：hero--008

今天在用merge into语句处理一个缓慢变化维度时，遇到了ora-02064：distributed operation not supported，这个错误的描述是说merge into不支持分布式事务操作

merge into server_list t1
using resdata.server_list@game_role_61 t2
on (t1.dbsvr_name = t2.dbsvr_name)
when not matched then
  insert
    (t1.server_name,
     t1.ip,
     t1.paysysid,
     t1.zone,
     t1.dbsvr_name,
     t1.logsvr,
     t1.status)
  values
    (t2.server_name,
     t2.ip,
     t2.paysysid,
     t2.zone,
     t2.dbsvr_name,
     t2.logsvr,
     t2.status);

google了下网上说是10g的一个bug， http://space.itpub.net/637736/viewspace-312612 再查询了oracle的官方文档却说支持这种操作： You can specify this clause(这里是说match_insert_clause) by itself or with the merge_update_clause. If you specify both, then they can be in either order. 解决的办法是：加上标红的语句加一个无实际意义的merge_update_clause,严格按照oracle的标准语法走

merge into server_list t1
using resdata.server_list@game_role_61 t2
on (t1.dbsvr_name = t2.dbsvr_name)
when matched then
  update set t1.server_name=t2.server_name
when not matched then
  insert
    (t1.server_name,
     t1.ip,
     t1.paysysid,
     t1.zone,
     t1.dbsvr_name,
     t1.logsvr,
     t1.status)
  values
    (t2.server_name,
     t2.ip,
     t2.paysysid,
     t2.zone,
     t2.dbsvr_name,
     t2.logsvr,
     t2.status);

另外的做法是重新用其他的语句(左链接)来写这个逻辑。