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:
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.
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.
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
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
Transitioning from DBMS_JOB to DBMS_SCHEDULER
To make the transition from using DBMS_JOB to DBMS_SCHEDULER,
I suggest the following steps:
Identify all currently active scheduled tasks that were scheduled
via DBMS_JOB, including their scheduled run times.
Analyze these tasks and identify any "overlaps" among
the DBMS_JOB scheduled tasks. These overlaps might include identical tasks
being run at different times; tasks being run in parallel; or tasks
being run serially.
Convert tasks scheduled via DBMS_JOB into their corresponding
DBMS_SCHEDULER components (program, schedule, and job objects):
Once the conversion is complete, then remove all the tasks using DBMS_JOB.REMOVE:.
As these examples have shown, Oracle 10g's new
Scheduler offers a whole new range of flexibility when scheduling tasks. In my
next article, I will delve into the details of scheduling tasks at non-standard
date and time intervals. I will also explore some of the advanced features of
the new Scheduler, including the ability to manage resource conflicts between
scheduled tasks with Scheduler windows and job classes.
References and Additional Reading
While there is no substitute for direct experience, reading
the manual is not a bad idea, either. I have drawn upon the following Oracle 10g
documentation for the deeper technical details of this article:
B10802-01 Oracle 10g
PL/SQL Packages and Types Reference
B10739-01 Oracle 10g
Concepts, Chapters 25-28
See All Articles by Columnist Jim Czuprynski