Oracle PL/SQL: When are Materialized Views refreshing?

This blog post provides a pieces of SQL which will look for Jobs in the SYS.DBA_JOBS view that appear to be Materialized View refreshes and display the timing information as well as the interval.


Execute the following SQL;

 SELECT dj.job,
       SUBSTR(SUBSTR(dj.what, 24, 255),
              1,
              INSTR(SUBSTR(dj.what, 25, 255), '"')) VIEW_OWNER,
       REPLACE(SUBSTR(SUBSTR(dj.what, 24, 255),
                      INSTR(SUBSTR(dj.what, 25, 255), '.') + 3,
                      255),
               '"'');',
               '') VIEW_NAME,
       dj.last_date,
       dj.last_sec,
       dj.NEXT_DATE,
       dj.broken,
       dj.interval
  FROM sys.dba_jobs dj
 WHERE dj.what like 'dbms_refresh.refresh(''"%"."%"'');'

This will return something similar to;

This gives enough information to allow new views to be scheduled in the gaps around the refreshes of old views and I have always found very useful.