求助:物化视图on commit报ORA-02051 ORA-02054
本帖最后由 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
$ 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.
看起来 你的 物化视图 的相关基表上可能有XA 操作，这会导致上述问题。 对于有这样情况一般只能用 ON DEMAND materialized views 刘大, 用on demand就不能随时更新, 要想提交更新,还有什么方法吗? 刘大,我不用物化视图了,改用触发器了做更新了.
页:
[1]