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.