Some useful queries / tricks around Oracle Materialized Views
Mar 28, 2012
2 minute read

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.

Source :

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
select count(*) c_star
from   m;

create materialized view m_mv_cstar
 on prebuilt table
 refresh fast
 on commit
select count(*) c_star
from   m;

insert into m values (1);


Source :

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

Source :


comments powered by Disqus