Some useful queries / tricks around Oracle Materialized Views
Get all materialized views
select * from user_mviews
Get latest refresh times for all materialized views
select * from user_mview_refresh_times
Get information on a log
select count(*) from mlog$_MyTable;
Get the list of all materialized views on a view log
SELECT master, owner, NAME, snapshot_site, TO_CHAR(current_snapshots,'mm/dd/yyyy hh24:mi') current_snapshots FROM user_registered_snapshots, user_snapshot_logs WHERE user_registered_snapshots.snapshot_id = user_snapshot_logs.snapshot_id (+)
First column is the master table and name is the materialized view name. An interesting information is the last date to check for never updated view and growing logs.
Refresh a view
execute DBMS_MVIEW.REFRESH ('MyTable', 'F');
You can replace the F (as Fast refresh) by a C to get a complete refresh.
Special care on view log
You may had to add WITH SEQUENCE to your log creation to cope with certain use as stated in Oracle documentation :
Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.
You can create a materialized view on a prebuild table
create table m (col1 number); create materialized view log on m with rowid (col1) including new values; create table m_mv_cstar as select count(*) c_star from m; create materialized view m_mv_cstar on prebuilt table refresh fast on commit as select count(*) c_star from m; insert into m values (1); commit;
If you need to refresh some materialized views through a db link on many schemas on the same database, be sure to give a different name to yours db links. If you don't you could have this error :
ORA-04068: existing state of packages has been discarded ORA-04062: of has been changed ORA-04062: timestamp of package "SYS.DBMS_SNAPSHOT_UTL" has been changed