Purging Materialized View Logs

Purging Materialized View Logs

Oracle automatically purges rows in the materialized view log when they are no longer needed. In the example below note how the log table is empty after the refresh.

create materialized view log on t ;

create materialized view mv
  refresh fast
  as select * from t
;

select count(*) from mlog$_t ;

  COUNT(*)
----------
         0

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

select count(*) from mlog$_t ;

  COUNT(*)
----------
         1

execute dbms_mview.refresh( list => 'MV', method => 'F' );

select count(*) from mlog$_t ;

  COUNT(*)
----------
         0
 

DBMS_MVEW.PURGE_LOG

If a materialized view log needs to be purged manually for some reason a procedure called DBMS_MVEW.PURGE_LOG can be used.

select count(*) from mlog$_t ;

  COUNT(*)
----------
         0

update t set val = 'X' where key = 5 ;
commit;

select count(*) from mlog$_t ;

  COUNT(*)
----------
         1

execute DBMS_MVIEW.PURGE_LOG( master => 'T', num => 9999, flag => 'delete' ) ;

select count(*) from mlog$_t ;

  COUNT(*)
----------
         0
 

The "num" and "flag" parameters can be used to partially purge the log. See the PURGE_LOG manual page for further details.

Once a materialized view log has been purged any materialized views dependent on the deleted rows cannot be fast refreshed. Attempting a fast refresh will raise an error.

execute dbms_mview.refresh( list => 'MV', method => 'F' );
BEGIN dbms_mview.refresh( list => 'MV', method => 'F' ); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 1


 

Such materialized views will need to be refreshed completely.

select * from mv ;
 

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
         5 e
 

execute dbms_mview.refresh( list => 'MV', method => 'C' );

select * from mv ;
 

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
         5 X
 

Cleanup

delete from t where key = 5 ;
commit;

drop materialized view mv ;

drop materialized view log on t ;