对有物化视图的基表执行分布式事务报错:ORA-02050,ORA-12048,ORA-02051

郑全

对有物化视图的基表执行分布式事务报错:ORA-02050,ORA-12048,ORA-02051
即不能通过dblink 的方式更新物化试图的基表


具体可以参见：
文档 1376282.1


附文档：


Ora-02050 ,ORA-02051,ORA-06512 Error while refreshing an ON COMMIT Materialized View (Doc ID 1376282.1)               

In this Document
Symptoms
Cause
Solution

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS

We have been getting ORA-02050 error


ORA-02050: transaction 10.12.42696 rolled back, some remote DBs may be in-doubt
ORA-02051: another session or branch in same transaction failed or finalized

Even after cleared the dba_2pc_pending rows before and after running the package and we still got the same error.


SQL> exec rmis_dmart.rmis_dmart_etl_pkg.delete_clis_emp_event;
BEGIN rmis_dmart.rmis_dmart_etl_pkg.delete_clis_emp_event; END;

*


ERROR at line 1:
ORA-02050: transaction 10.14.43833 rolled back, some remote DBs may be in-doubt
ORA-02051: another session or branch in same transaction failed or finalized
ORA-06512: at "RMIS_DMART.RMIS_DMART_ETL_PKG", line 4497
ORA-06512: at line 1


CAUSE
Restrictions on Refreshing ON COMMIT :-

If you have an "ON COMMIT " Materialized View , then you cannot subsequently execute a distributed transaction on any base table of this materialized view.

For example, you cannot insert into the base by selecting from a remote table.

SOLUTION
Drop the ON COMMIT materialized view.

Restrictions on Refreshing ON COMMIT

- This clause is not supported for materialized views containing object types or Oracle-supplied types.
- This clause is not supported for materialized views with remote tables.
- If you specify this clause, then you cannot subsequently execute a distributed transaction on any  base     table of this materialized view.
  For example, you cannot insert into the base by selecting from a remote table.
The ON DEMAND clause does not impose this restriction on subsequent distributed transactions on base tables.