Tuesday, September 6, 2011

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),
              INSTR(SUBSTR(dj.what, 25, 255), '"')) VIEW_OWNER,
       REPLACE(SUBSTR(SUBSTR(dj.what, 24, 255),
                      INSTR(SUBSTR(dj.what, 25, 255), '.') + 3,
               '') VIEW_NAME,
  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.
Post a Comment