In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.

create materialized view log on t ;

create materialized view mv
  REFRESH FAST ON COMMIT
  as select * from t
;

select rowid, key, val from mv ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAWpkAAEAAAAaMAAA          1 a
AAAWpkAAEAAAAaMAAB          2 b
AAAWpkAAEAAAAaMAAC          3 c
AAAWpkAAEAAAAaMAAD          4
 

Let's see what happens to the view in the course of an insert operation.

insert into t values ( 5, 'e' );

select rowid, key, val from mv ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAWpkAAEAAAAaMAAA          1 a
AAAWpkAAEAAAAaMAAB          2 b
AAAWpkAAEAAAAaMAAC          3 c
AAAWpkAAEAAAAaMAAD          4
 

Nothing happend yet. Let's issue a COMMIT.

commit;

select rowid, key, val from mv ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAWpkAAEAAAAaMAAA          1 a
AAAWpkAAEAAAAaMAAB          2 b
AAAWpkAAEAAAAaMAAC          3 c
AAAWpkAAEAAAAaMAAD          4
AAAWpkAAEAAAAaPAAA          5 e
 

Note how the materialized view was automatically fast refreshed after the COMMIT command. No call to DBMS_MVIEW.REFRESH was required.

Restrictions

Materialized views can only refresh ON COMMIT in certain situations.

1. The materialized view must be FAST refreshable.
2. The materialized view cannot contain object types or Oracle-supplied types.
3. The base tables will never have any distributed transactions applied to them.

The first two cases will produce an error during the CREATE MATERIALIZED VIEW command.

-- this materialized view is not fast refreshable
-- because there is no materialized view log on the base table

create materialized view mv2
  REFRESH FAST ON COMMIT
  as select max( val ) from t
;
  as select max( val ) from t
                            *
ERROR at line 3:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T"



-- this materialized view is not fast refreshable
-- because the materialized view contains an Oracle-supplied type

create materialized view mv2
  REFRESH FAST ON COMMIT
  as select key, val, sys_xmlgen( val ) as val_xml from t
;
  as select key, val, sys_xmlgen( val ) as val_xml from t
                                                        *
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


 

The third case generates an error when a distributed transaction is attempted on the base table. In the following example materialized view MV (created at the top of this page) was created with REFRESH FAST. Attempting a distributed transaction on its base table, T, will therefore raise an error.

insert into t select key+10, val from T@REMOTE ;
commit;
commit
*
ERROR at line 1:
ORA-02050: transaction 6.24.5341 rolled back, some remote DBs may be in-doubt
ORA-02051: another session in same transaction failed


 

(REMOTE is a database link which loops back to the current account.)

ON DEMAND materialized views have no such restriction.

alter materialized view mv refresh ON DEMAND ;

insert into t select key+10, val from T@REMOTE ;
commit;

select * from t ;
 

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
         5 e
        11 a
        12 b
        13 c
        14
        15 e
 

Cleanup

drop materialized view mv ;

drop materialized view log on t ;

delete from t where key >= 5 ;
commit ;