A Materialized View refresh synchronizes the MVIEW with it’s master table. In the case of updateable MVIEWs, changes from the MVIEW site are first propagated to master site if the refresh’s push_deferred_rpc parameter is TRUE. Then, changes are pulled from the master site. Pulling rows from master can be either performed using the mview log on the master table (this type of refresh is a FAST refresh), or without using the mview log (this type of refresh is a COMPLETE refresh).
If the refresh is done automatically by a job queue process or by manually executing dbms_job.run(), then finding the next and last refresh times of a refresh can be accomplished by querying dba_jobs as follows:
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
column what format a36
select what, last_date, next_date
from dba_jobs
where upper(what) like 'ÛMS_REFRESH.REFRESH(%%.%%';
WHAT LAST_DATE NEXT_DATE
------------------------------------ ------------------- -------------------
dbms_refresh.refresh('"TESTUSER"."R"'); 10-02-2003 10:48:46 11-02-2003 10:48:46
If the refresh is not performed via dbms_job then the next date can still be determined using the query above. However, the last refresh date will not show the correct date. In this case, last refresh date can be obtained by querying dba_snapshot_refresh_times for each MVIEW of the refresh group.
select rt.owner, rt.name, rt.last_refresh from dba_refresh_children rc, dba_snapshot_refresh_times rt where rc.owner=rt.owner and rc.name =rt.name and rc.rname='' and rc.owner=''; OWNER NAME LAST_REFRESH --------------- ------------------------------ ------------------- TESTUSER TABLE1 10-02-2003 10:41:19 TESTUSER TABLE2 10-02-2003 10:41:19
Determine if a Specific MVIEW is Being Refreshed
This can be accomplished by examining V$LOCK for any JI type lock acquired on the MVIEW in question.
column owner format a15 column username format a15 column mview format a15 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'; OWNER MVIEW USERNAME SID --------------- --------------- --------------- ---------- TESTUSER TABLE1 TESTUSER 16