How to Monitor the Progress of a Materialized View Refresh ??
2 min read
Determine if a Specific MVIEW is Being Refreshed:
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
Determine if a Refresh Group is Being Refreshed
There are two possible ways of refreshing a refresh group:
-> refresh is being run by a job queue process in the background.
-> refresh is being run manually inside Sql*Plus or another tool.
Given the name of the refresh group and its owner, the following query can be used to identify if a refresh is being executed by a job queue process:
select s.sid, s.username
from dba_jobs_running jr, v$session s, dba_jobs j
where jr.sid=s.sid and
j.job=jr.job and
upper(j.what) like '%REFRESH%%';
SID USERNAME
---------- ---------------
16 TESTUSER
Identify the Last and Next Refresh Dates
:
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-2023 10:48:46 11-02-2023 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-2023 10:41:19
TESTUSER TABLE2 10-02-2023 10:41:19
