Wednesday, September 30, 2015

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>

Thursday, September 17, 2015

ORA-01440

I was asked to run some SQL today and it generated an ORA-01440. I did not remember having seen this error before so I decided to check it out in an Oracle 11.2 database. First I created a table and added a row of data to it:

SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
 2  /
 
1 row created.
 
SQL>

Then I tried to change col1 to number(5), Oracle gave me an ORA-01440. The reason for this should be obvious:

SQL> alter table tab1 modify (col1 number(5))
  2  /
alter table tab1 modify (col1 number(5))
                         *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
 
SQL>
 
I removed the data from the table and when I tried to modify it again, I did not get an error:

SQL> delete tab1
  2  /
 
1 row deleted.
 
SQL> alter table tab1 modify (col1 number(5))
  2  /
 
Table altered.
 
SQL>

Instead of removing the data, I could have set the value(s) to null:
 
SQL> drop table tab1
  2  /
 
Table dropped.
 
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> alter table tab1 modify (col1 number(5))
  2  /
alter table tab1 modify (col1 number(5))
                         *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
 
SQL> update tab1 set col1 = null
  2  /
 
1 row updated.
 
SQL> alter table tab1 modify (col1 number(5))
  2  /
 
Table altered.
 
SQL>

Whichever method you choose, you should consider saving the data beforehand and reinstating it afterwards.