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
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