12.9. Using Scheduler Views

Oracle offers a wide variety of views to access information regarding the Scheduler and its associated objects. These views allow you to see information about currently running jobs and past runs of jobs. Table 12.6 describes the available Scheduler views.

To see information on completed instances of a job, use the example shown here:

SQL> select job_name, status, error#
  2  from dba_scheduler_job_run_details
  3  where job_name = 'FAIL_JOB';

JOB_NAME STATUS           ERROR#
-------- --------------   ------
FAIL_JOB FAILURE           20000

To see the current state of all jobs, use the following example:

SQL> select job_name, state
  2  from dba_scheduler_jobs;

JOB_NAME          STATE
----------------- ---------------
PURGE_LOG         SCHEDULED
GATHER_STATS_JOB  SCHEDULED
COLA_JOB          SCHEDULED
RAISE_JOB         DISABLED

Table 12.6. Scheduler Views Available
ViewDescription
*_SCHEDULER_SCHEDULESShows information on all defined schedules.
*_SCHEDULER_PROGRAMSShows information on all defined programs.
*_SCHEDULER_PROGRAM_ARGUMENTSShows all registered program arguments, and the default values if they exist.
*_SCHEDULER_JOBSShows all defined jobs, both enabled and disabled.
*_SCHEDULER_GLOBAL_ATTRIBUTEShows the current values of all Scheduler attributes.
*_SCHEDULER_JOB_ARGUMENTSShows the arguments for all defined jobs.
*_SCHEDULER_JOB_CLASSESShows information on all defined job classes.
*_SCHEDULER_WINDOWSShows information about all defined windows.
*_SCHEDULER_JOB_RUN_DETAILSShows information about all completed (failed or successful) job runs.
*_SCHEDULER_WINDOW_GROUPSShows information about all window groups.
*_SCHEDULER_WINGROUP_MEMBERSShows the members of all window groups.
*_SCHEDULER_RUNNING_JOBSShows the state information on all jobs that are currently being run.

To view windows and their next start dates, the following SQL can be used:

SQL> select window_name, next_start_date
  2  from dba_scheduler_windows;

WINDOW_NAME          NEXT_START_DATE
-------------------- ------------------------------------
WEEKNIGHT_WINDOW     12-OCT-04 10.00.00.300000 PM −08:00
WEEKEND_WINDOW       16-OCT-04 12.00.00.500000 AM −08:00

The DBA_SCHEDULER_JOB_LOG view can be used to view log entries for previously executed jobs, as shown here:

SQL> select log_id, trunc(log_date) log_date, owner, job_name, operation
  
from dba_scheduler_job_log;

LOG_ID  LOG_DATE   OWNER JOB_NAME           OPERATION
------- ---------- ----- ----------------- ---------
    522 25-SEP-04  SYS     PURGE_LOG        RUN
    524 25-SEP-04  SYS     ADV_SQL_TUNING   SUCCEEDED
    525 25-SEP-04  SYS     ADV_SQL_TUNING   DROP
    528 25-SEP-04  SYS     GATHER_STATS_JOB RUN
    484 18-SEP-04  SYS     GATHER_STATS_JOB RUN
    541 26-SEP-04  SYS     PURGE_LOG RUN
    543 27-SEP-04  SYS     PURGE_LOG RUN
    545 28-SEP-04  SYS     GATHER_STATS_JOB RUN
    546 28-SEP-04  SYS     PURGE_LOG RUN
    553 30-SEP-04  SYS     GATHER_STATS_JOB RUN
    622 10-OCT-04  BUTERTB COLA_JOB RUN
    549 29-SEP-04  SYS     GATHER_STATS_JOB RUN

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset