Tuesday, December 11, 2007

Materialized Views & Refresh Groups

mviews & refresh groups queries

The follwoing query (9i and onwards) shows which mview is refreshed the current time.

select currmvowner, currmvname
from v$mvrefresh;


begin
DBMS_MVIEW.EXPLAIN_MVIEW('MVIEW_NAME');
end;
/

Analyzes mview and by querying the MV_CAPABILITIES_TABLE you can see the details of the analysis e.g if mview is fast refreshable.

Refresh Groups

All refresh groups with its mviews

select r.rowner, r.rname, r.job, c.name,
r.next_date next_refresh, r.broken
from all_refresh r, all_refresh_children c
where r.job = c.job;

select o.owner, o.object_name mview, username, s.sid
from v$lock l, dba_objects o, v$session s
where o.object_id=l.id1 and
l.type='JI' and
l.lmode=6 and
s.sid=l.sid and
o.object_type='TABLE';

If u use refresh groups the above query shows the locks on the base tables of mview so you can see if the refresh group is running.

No comments:

Post a Comment