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>
