ON COMMIT Materialized View Causes ORA-02050 and ORA-02051

This example replicates a problem I discovered today. I tested it in an Oracle 11.2 database. First I created an empty table called tab1:

SQL> create table tab1

  2  (col1 varchar2(30),

  3   constraint con1 primary key (col1))

  4  /

 

Table created.

 

SQL>

Then I set up a materialized view on the table. Note that it is refreshed on commit:

SQL> create materialized view log on tab1

  2  /

 

Materialized view log created.

 

SQL> create materialized view mv1

  2  refresh fast on commit

  3  as select * from tab1

  4  /

 

Materialized view created.

 

SQL>

I added some rows to the table FROM THE LOCAL DATABASE:

SQL> insert into tab1

  2  select table_name

  3  from dba_tables

  4  where owner = 'SYS'

  5  and table_name like 'A%'

  6  /

 

97 rows created.

 

SQL>

I checked that the rows did not appear in the materialized view:

SQL> select count(*) from mv1

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

Then I did a commit to refresh the materialized view:

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

… and checked that I could see the rows in the table via the materialized view:

SQL> select count(*) from mv1

  2  /

 

  COUNT(*)

----------

        97

 

SQL>

I inserted some more rows into the table FROM A REMOTE DATABASE:

SQL> insert into tab1

  2  select table_name

  3  from dba_tables@remote_database

  4  where owner = 'SYS'

  5  and table_name like 'S%'

  6  /

 

142 rows created.

 

SQL>

 

I checked that I could not see these new rows via the materialized view:

 

SQL> select count(*) from mv1

  2  /

 

  COUNT(*)

----------

        97

 

SQL>

 

If you have an on commit materialized view, you cannot insert rows into any of its master tables from a remote database. (I read this on My Oracle Support.) So when I tried to refresh the materialized view, I got an ORA-02050 and an ORA-02051:

 

SQL> commit

  2  /

commit

*

ERROR at line 1:

ORA-02050: transaction 6.25.157910 rolled back, some

remote DBs may be in-doubt

ORA-02051: another session or branch in same

transaction failed or finalized

 

SQL>

 

… and the number of rows in the materialized view did not change:

 

SQL> select count(*) from mv1

  2  /

 

  COUNT(*)

----------

        97

 

SQL> 

I also noticed the following messages in the alert log: 

Wed Sep 30 17:41:31 2015

Following on-commit snapshots not refreshed :

ANDREW.MV1

Error 2051 trapped in 2PC on transaction 6.25.157910. Cleaning up.

Error stack returned to user:

Wed Sep 30 17:41:31 2015

DISTRIB TRAN BUSDPT1.WORLD.2e3bf2d1.6.25.157910

ORA-02050: transaction 6.25.157910 rolled back, some remote DBs may be in-doubt

  is local tran 6.25.157910 (hex=06.19.268d6)

ORA-02051: another session or branch in same transaction failed or finalized

  insert pending collecting tran, scn=1759205570084 (hex=199.98d2b624)

Wed Sep 30 17:41:31 2015

DISTRIB TRAN BUSDPT1.WORLD.2e3bf2d1.6.25.157910

  is local tran 6.25.157910 (hex=06.19.268d6))

  delete pending collecting tran, scn=1759205570084 (hex=199.98d2b624) 

To isolate the exact cause, I created another table called tab2:

SQL> create table tab2

  2  (col2 varchar2(30),

  3   constraint con2 primary key (col2))

  4  /

 

Table created.

 

SQL>

 

Then I set up an on demand materialized view on the table:

 

SQL> create materialized view log on tab2

  2  /

 

Materialized view log created.

 

SQL> create materialized view mv2

  2  refresh fast on demand

  3  as select * from tab2

  4  /

 

Materialized view created.

 

SQL>

 

I inserted the same rows into the table FROM THE LOCAL DATABASE:

 

SQL> insert into tab2

  2  select table_name

  3  from dba_tables

  4  where owner = 'SYS'

  5  and table_name like 'A%'

  6  /

 

97 rows created.

 

SQL>

 

… and checked that I could not see them via the materialized view:

 

SQL> select count(*) from mv2

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

 

I did a commit:

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

 

… but this had no effect on the materialized view as it was refreshed on demand:

 

SQL> select count(*) from mv2

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

 

… so I did an on demand refresh:

 

SQL> exec dbms_mview.refresh('mv2');

 

PL/SQL procedure successfully completed.

 

SQL>

 

… and checked that I could see the rows I had added to the table:

 

SQL> select count(*) from mv2

  2  /

 

  COUNT(*)

----------

        97

 

SQL>

 

I added the same rows FROM THE REMOTE DATABASE:

 

SQL> insert into tab2

  2  select table_name

  3  from dba_tables@remote_database

  4  where owner = 'SYS'

  5  and table_name like 'S%'

  6  /

 

142 rows created.

 

SQL>

 

… but when I did a commit, this time it did not fail:

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

 

Of course, as the materialized view had to be refreshed on demand, the new rows did not appear in it:

 

SQL> select count(*) from mv2

  2  /

 

  COUNT(*)

----------

        97

 

SQL>

 

… so I did another on demand refresh:

 

SQL> exec dbms_mview.refresh('mv2');

 

PL/SQL procedure successfully completed.

 

SQL>

 

… and then I could see them:

 

SQL> select count(*) from mv2

  2  /

 

  COUNT(*)

----------

       239

 

SQL>