ora 600 4080 when update the base table of the materialized VIEW whit on commit-优快云博客

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 ora 600 4080 when update the base table of the materialized VIEW whit on commit

the error shows like that:

java.sql.SQLException: ORA-02050: 事务处理 1.8.583 已回退, 某些远程数据库可能有问题

ORA-12048: 刷新实体化视图 "ZDDX32"."MV_DDBJ" 时出错

ORA-00600: 内部错误代码, 参数: [4080], [1], [131], [], [], [], [], []

DSRA0010E: SQL 状态 = 42000，错误代码 = 2,050

And the definition of materialized VIEW MV_DDBJ like that:

create materialized view MV_CZBJ

refresh fast on commit

select ckyj.rowid row_id,'常控预警' ywlx,ckyj.zlxxid ywid,ckyj.yjjb yjjb, ckyj.zdrylbbj rylb,ckyj.dtxxlx dtxxlx,ckyj.zdryxl zdryxl, ckyj.djxm xm,ckyj.djzjhm sfzh,ckyj.qszt||ckyj.fkzt qsfkzt,ckyj.xxbdsj xxbdsj,ckyj.jszldwdm xqdm,YQCZCS yqczcs,yqfksj yqfksj,ZLFBSJ ZLFBSJ,XXBDDW XXLY,decode(ckyj.thcfbz,'','0','0','1','1','0') thbz from t_cggk_yjzlxx ckyj where ckyj.fkzt = '0' or ckyj.thcfbz ='0'

union all

select lkyj.rowid row_id,'临控预警' ywlx,lkyj.yjzlxxid ywid,lkyj.zljb yjjb, '临控人员' zdrylb,lkyj.hdxxlb dtxxlx,'' zdryxl,lkyj.djxm xm, lkyj.djzjhm sfzh, lkyj.qszt||lkyj.fkzt qsfkzt, lkyj.xxbdsj xxbdsj, lkyj.jszldwdm xqdm,YQCZCS yqczcs,yqfksj yqfksj,ZLFBSJ ZLFBSJ,XXBDDW XXLY,decode(lkyj.thcfbz,'','0','0','1','1','0') thbz from t_lsbk_ry_yjzlxx lkyj where lkyj.fkzt = '0' or lkyj.thcfbz='0'

union all

select lkzl.rowid row_id,'临控指令' ywlx,lkzl.zlxxid ywid,lkzl.zljb yjjb, '临控人员' zdrylb,'' dtxxlx,'' zdryxl, lkzl.bbkrxm xm, lkzl.bbkrsjhm sfzh, lkzl.qszt||lkzl.fkzt qsfkzt,lkzl.zlfbsj, lkzl.jszldwdm xqdm,CZCSLX yqczcs,YQFKSJ yqfksj,ZLFBSJ ZLFBSJ,ZLFSDWDM XXLY,'0' thbz from t_lsbk_ry_bkzlxx lkzl where lkzl.fkzt='0';

through the metalink ,I find the cause of reason and the suggestion:(metalink:ID 1385495.8)

Description:

Distributed transactions are not allowed to update tables which have "ON COMMIT" snapshots / materialized views on them. In Oracle9i and ORA-600 [4080] is signalled at commit time. In Oracle8i the materialized view is not updated and so becomes out of sync with the master table.

Workaround:

Use ON DEMAND materialized views instead.

I do not know why they should use on commit here.I think on commit here may be not use.and they can use the view (not materialized VIEW) instead.the only thing I think is that they want to create the index on the materialized VIEW.we can use the optimization algorithm of the query instead.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14730395/viewspace-682470/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14730395/viewspace-682470/