The Oracle 10g Scheduler, Part 2: Implementation - Page 2

Viewing Scheduler Information

One of my pet peeves about DBMS_JOB is that only two views are provided to view the entire state of all scheduled tasks for the database. DBA_JOBS shows the current and future state of all scheduled tasks, and DBA_JOBS_RUNNING shows which tasks are running right now. Moreover, while DBA_JOBS can be queried to tell when a scheduled task last ran, it provides no information about how many times the task ran in the past, how many times it may have failed, and much more importantly why it failed.

Happily, the new Scheduler has rectified this by providing a plethora of views about its current and future state. I've assembled some queries that I have found useful in determining what Scheduler objects exist, what tasks have been scheduled, and the success or failure of each scheduled task activity after it has been run.

Scheduler Components. First, remember that all Scheduler components are actual database objects. The query in Listing 2.1 will show what jobs, programs, and schedules currently exist within the database:

                               Scheduler Objects:

         Object
Owner    Type         Name                 Created On           Status
-------- ------------ -------------------- -------------------- -------SYS
   JOB    GATHER_STATS_JOB   09/15/2004 20:47:25  VALID
SYS      JOB          HR_FRESHENSCHEMA     09/21/2004 17:46:29  VALID
SYS      JOB          PURGE_LOG            09/15/2004 20:26:09  VALID
SYS      JOB          SH_FRESHENSCHEMA     09/21/2004 17:48:02  VALID
SYS      JOB          SLSMGR_FRESHENSCHEMA 09/23/2004 19:13:13  VALID
SYS      JOB CLASS    AUTO_TASKS_JOB_CLASS 09/15/2004 20:47:25  VALID
SYS      JOB CLASS    DEFAULT_JOB_CLASS    09/15/2004 20:26:09  VALID
SYS      PROGRAM      FRESHENALLSCHEMAS    09/23/2004 18:54:50  VALID
SYS      PROGRAM      FRESHENSCHEMA        09/23/2004 18:57:57  VALID
SYS      PROGRAM      GATHER_STATS_PROG    09/15/2004 20:47:22  VALID
SYS      PROGRAM      PURGE_LOG_PROG       09/15/2004 20:26:09  VALID
SYS      SCHEDULE     DAILY_PURGE_SCHEDULE 09/15/2004 20:26:09  VALID
SYS      SCHEDULE     FRESHENSCHEMAS       09/19/2004 17:13:18  VALID
SYS      WINDOW       WEEKEND_WINDOW       09/15/2004 20:47:22  VALID
SYS      WINDOW       WEEKNIGHT_WINDOW     09/15/2004 20:47:18  VALID

15 rows selected.

Schedule Objects. The view DBA_SCHEDULER_SCHEDULES provides me with information about the schedules that are in effect in the database, as returned by the query in Listing 2.2.

Program Objects. View DBA_SCHEDULER_PROGRAMS shows all program objects and their attributes, while view DBA_SCHEDULER_PROGRAM_ARGS shows all program arguments for programs that have them. Listing 2.3 displays two sample queries against these two views.

Job Objects. Likewise, view DBA_SCHEDULER_JOBS shows all job objects and their attributes, while view DBA_SCHEDULER_JOB_ARGS shows the arguments that have been provided for each job that requires arguments. Listing 2.4 contains three queries for probing this information.

Scheduled Tasks Status and History. Three views track the status of all scheduled task activity for the database, including detailed history of each scheduled task's successful (or unsuccessful!) execution. This is a welcome addition to any DBA's tool belt for solving problems with scheduled tasks that may have failed unexpectedly. Listing 2.5 shows several +sample queries for gathering information on scheduled task execution, history, and current status.

View DBA_SCHEDULER_JOB_LOG shows a high-level summary of all job activity; its ADDITIONAL_INFO column is a CLOB that contains detailed information about the last run of the scheduled task.

View DBA_SCHEDULER_JOB_RUN_DETAILS shows an even greater level of detail about scheduled task run history; its ADDITIONAL_INFO column shows a brief description of what went wrong when a scheduled task has failed during execution.

Finally, view DBA_SCHEDULER_RUNNING_JOBS provides a method to look at a scheduled task while it is executing; this view replaces the old DBA_JOBS_RUNNING view.

Transitioning from DBMS_JOB to DBMS_SCHEDULER

To make the transition from using DBMS_JOB to DBMS_SCHEDULER, I suggest the following steps:

BEGIN
DBMS_JOB.REMOVE(<job>, TRUE);
...
COMMIT;
END;
/