How to know if MATERIALIZED VIEW update is running?

Question

I'm talking about oracle. I have a few materialized views, and they're updated from time to time (it is done with a scheduled task). It is easy to know last refresh date - just query USER_MVIEW_REFRESH_TIMES. And is there any way to know if some views are being updated in the current moment?

Answer 1

Hi Andrew,

you could see indirectly if a materialized view is being refreshed by looking if a process has aquired locks on it : a materialized view should be modified only when refreshed (except FOR UPDATE materialized views used for two-way replication).

This query should return rows only when your materialized view is being refreshed:

SELECT sid
  FROM v$lock
 WHERE type = 'TX'
   AND id1 = (SELECT object_id
                FROM all_objects
               WHERE owner = :owner
                 AND object_name = :object_name)

Answer 2

Materialized views that are defined to be refreshed on a schedule with "... START WITH... NEXT..." as part of their DDL will be executing under DBMS_JOB control. If the refresh of these MV's is in progress, you'll see values for THIS_DATE and THIS_SEC in the USER_JOBS (or DBA_JOBS) view for the corresponding job and/or a row in DBA_JOBS_RUNNING.

If you've defined the MV to be refreshed on demand and are launching the refresh externally then Vincent's answer is one way to determine the state.