zxjlqh

0 积分	1 好友	9 主题

发消息

[常规管理] 求助:物化视图on commit报ORA-02051 ORA-02054

1^#

发表于 2016-9-23 16:34:14 | 查看: 5051| 回复: 4

本帖最后由 zxjlqh 于 2016-9-23 16:38 编辑

RAC 双节点 11.2.0.4
Redhat5.8 64位

问题: 直接在数据库中对sd_merch_base_info做操作,会更新视图. 用管理后台插入数据后不能更新视图.

报错:
Following on-commit snapshots not refreshed :
SZT.MV_MERCH_INFO
Error 2051 trapped in 2PC on transaction 30.16.43853. Cleaning up.
Error stack returned to user:
ORA-02054:事务处理有问题30.16.43853
ORA-02051: 同一事务处理中的另一会话失败

创建物化视图sql
create materialized view mv_merch_info
refresh fast on commit as
select t.merch_id       as merchId,
   t.register_name as registerName,
   t.name_short    as nameShort,
   mt.typename    as merType,
   mt2.typename    as childType,
   tsc.areaname    as provice,
   tsc2.areaname    as region,
   t.deal_time    as contractDate,
   te.empname       as contracter,
   t.status,
   t.contractno,
   t.merchsys_status as merchsysStatus,
   t.rowid          t_rowid,
   mt.rowid       mt_rowid,
   mt2.rowid       mt2_rowid,
   tsc.rowid       tsc_rowid,
   tsc2.rowid       tsc2_rowid,
   te.rowid       te_rowid
  from sd_merch_base_info t,
   sd_merch_type    mt,
   sd_merch_type    mt2,
   tsys_area_code    tsc,
   tsys_area_code    tsc2,
   tsys_employee    te
where mt.typecode(+) = t.mertype
and mt2.typecode(+) = t.childtype
and tsc.areacode(+) = t.provice
and tsc2.areacode(+) = t.region
and te.empno(+) = t.contracter

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2016-9-23 17:06:14

[oracle@ocp ~]$ oerr ora 02051
02051, 00000, "another session or branch in same transaction failed or finalized"
// *Cause: A session at the same site with the same global transaction ID failed.
//       The failure could also be caused by an application error if an attempt
//       was made to update the database while another tightly coupled transaction
//       branch with the same global transaction ID was finalized or aborted.
// *Action: No action necessary as the transaction will be automatically recovered.
//       In the case of an application error, make sure there are no more updates
//       to the database once the transaction manager has started two-phase commit
//       for tightly coupled distributed transaction.

Do not create ON COMMIT materialized views on top of tables that are going to be modified by XA operations. there is no way to skip this restriction.

Workaround

Use ON DEMAND materialized views, which refresh operation does not suffer this restriction.

回复只看该作者道具举报