Determining the Job ID for Each Refresh Job at a Materialized View Site

Determining the Job ID for Each Refresh Job at a Materialized View Site

You can use the query in this section to list the following information about the refresh jobs at a materialized view site:

• The job identification number of each refresh job. Each job created by the DBMS_JOB package is assigned a unique identification number.
• The privilege schema, which is the schema whose default privileges apply to the job.
• The schema that owns each refresh job. Typically, the materialized view administrator owns a refresh job. A common username for the materialized view administrator is mviewadmin.
• The name of the refresh group that the job refreshes.
• The status of the refresh job, either normal or broken.

The following query displays this information:

COLUMN JOB HEADING 'Job ID' FORMAT 999999
COLUMN PRIV_USER HEADING 'Privilege|Schema' FORMAT A10
COLUMN RNAME HEADING 'Refresh|Group|Name' FORMAT A10
COLUMN ROWNER HEADING 'Refresh|Group|Owner' FORMAT A10
COLUMN BROKEN HEADING 'Broken?' FORMAT A7

SELECT J.JOB, 
       J.PRIV_USER, 
       R.ROWNER, 
       R.RNAME, 
       J.BROKEN
    FROM DBA_REFRESH R, DBA_JOBS J 
    WHERE R.JOB = J.JOB
    ORDER BY 1;

Your output looks similar to the following:

                   Refresh    Refresh
        Privilege  Group      Group
 Job ID Schema     Owner      Name       Broken?
------- ---------- ---------- ---------- -------
     21 MVIEWADMIN MVIEWADMIN HR_REFG    N

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