DW: Some useful system views when checking Materialized View

1. dba_mview_refresh_times/user_mview_refresh_times

This view lists the master table, mv and the last refresh time. When mv are fast refresh mv, the mv log will be purged only when all the mvs are refreshed and those logs are no longer useful.

 

2. mv log naming format

The materialized view log actually is a table , we can find it in sys.obj$, the name format is MLOG$_<TABLE_NAME> , this name can be found in user_snapshot_logs view. in this table, dmltype is recorded as U, I and D. it also has a snapshot timestamp, I believe Oracle refreshes mv by comparing the last_refresh time with the sanpshot timestamp in this log, it picks rows with snapshot timestamp later then the last refresh time. 

 

3. mv log and internal trigger

oracle captures changes in the table by internal trigger, unforutnately, I'm not able to find the trigger and it's source. The only information I can find is DBA_INTERNAL_TRIGGERS view. according to the view source ,the trigflag in sys.tab$ table reveals the type of such trigger.

 

4. filter column when create mv log.

create table mytest (a number, b number,c number);

When we create mv log

CREATE MATERIALIZED VIEW LOG ON mytest
PCTFREE 0
PCTUSED 99
WITH ROWID(A,B);

 

Here we use ROWID(A,B), in the mv log MLOG$_MYTEST, we can see the A, B columns are included. This meaningful columns is useful in system like Oracle 11i in which most tables has no primary key but unique key.