Listing Information About the Refresh Groups at a Materialized View Site

Listing Information About the Refresh Groups at a Materialized View Site

Each refresh group at a materialized view site is associated with a refresh job that refreshes the materialized views in the refresh group at a set interval. You can query the DBA_REFRESH data dictionary view to list the following information about the refresh jobs at a materialized view site:

• The name of the refresh group.
• The owner of the refresh group.
• Whether the refresh job is broken.
• The next date and time when the refresh job will run.
• The current interval setting for the refresh job. The interval setting specifies the amount of time between the start of a job and the next start of the same job.

The following query displays this information:

COLUMN RNAME HEADING 'Refresh|Group|Name' FORMAT A10
COLUMN ROWNER HEADING 'Refresh|Group|Owner' FORMAT A10
COLUMN BROKEN HEADING 'Broken?' FORMAT A7
COLUMN next_refresh HEADING 'Next Refresh'
COLUMN INTERVAL HEADING 'Interval' FORMAT A20

SELECT RNAME, 
               ROWNER, 
       BROKEN, 
       TO_CHAR(NEXT_DATE, 'DD-MON-YYYY HH:MI:SS AM') next_refresh, 
       INTERVAL 
    FROM DBA_REFRESH 
    ORDER BY 1;

Your output looks similar to the following:

Refresh    Refresh
Group      Group
Name       Owner      Broken? Next Refresh            Interval
---------- ---------- ------- ----------------------- --------------------
HR_REFG    MVIEWADMIN N       24-OCT-2003 07:18:44 AM SYSDATE + 1/24

The N in the Broken? column means that the job is not broken. Therefore, the refresh job will run at the next start time. A Y in this column means that the job is broken.