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

You could try:

SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH

to get the list of views refreshing right now.

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.