Oracle Scheduling Job Run Details

Job Run Details

Every row in the dba_scheduler_job_log view for a run event has an associated row in the dba_scheduler_job_run_details view.  This view provides more details about the job run including requested start date, actual start date, duration and CPU usage etc.

The scheduled_job_details.sql and job_run_history.sql scripts from Chapter 5 use the dba_scheduler_job_run_details view to display the run history for a specific job.

SQL> @job_run_history all 5

JOB_NAME                 OWNER    STATUS    COMPLETION_DATE      RUN_DURATION
------------------------ -------- --------- -------------------- ------------
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:31:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:29:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:27:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:25:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:23:43            1

5 rows selected.

The job logs and job run detail logs allow the tracking of all job activity but do not reveal what the active resource plan was during the job runs.  This information is provided by the window logs, which is covered in the next section.

Window Logs

There is no logging level associated with window logs.  A window log entry is created whenever a window is created, dropped, opened, closed, overlapped, disabled or enabled.  The window_logs.sql script uses the dba_scheduler_window_log view to display window log information about a specific window, or all windows.

* window_logs.sql

-- -- Parameters:
--    1) Specific window name or �all� windows.
-- *****************************************************************

set feedback off
alter session set nls_timestamp_tz_format='DD-MON-YYYY HH24:MI:SS';
set feedback on

column window_name format a30
column operation format a10
column status format a10
column log_date format a27

select
   window_name,
   operation,
   status,
   log_date
from
   dba_scheduler_window_log
where
   window_name = decode(upper('&1'), 'ALL', window_name, upper('&1'))
order by
   log_date
;

An example of the output produced by this script is shown below.

Each entry in the dba_scheduler_window_log view for a close operation has an associated entry in the dba_scheduler_window_details view.  This view provides additional information including the requested start date, actual start date and window duration.  The window_details.sql script makes use of this view.

An example of the output produced by this script is shown below.

Now that the various types of scheduler logs have been introduced, it makes sense to examine their management.  It has been shown that the contents of the scheduler logs are managed automatically, but the next section will illustrate how to manually purge the scheduler logs.

Purging Logs

On occasion, it may be necessary to manually purge the scheduler logs prior to any regularly scheduled automatic purge.  This can be accomplished using the purge_log procedure.

The parameters and usages associated with this procedure are listed below:

* log_history - This determines the age of the logs that should be kept. Valid values range between zero and 999 with the default being zero.

* which_log - This indicates which log or logs should be purged. The possible parameters are job_log, window_log and job_and_window_log, with the latter being the default value.

* job_name - This limits the purge operation of a specific job, job class or comma separated list. By default, this parameter is set to NULL, which indicates logs for all jobs should be purged.

The following example shows how the procedure can be used:

To purge all entries for both the job and window logs, simply call the procedure with no parameters.

The auto_purge procedure uses the log_history values defined at the scheduler, job class and job log_history level to determine which logs should be purged. This procedure runs as part of the scheduled purge process, but can also be run manually.

Depending on the circumstances, it is possible to manage the purging of logs manually as well as automatically.  Moving on to the management of resources, it was mentioned previously that windows are involved in the link between the scheduler and the resource manager.  In the following section, the link between these two functional areas will be examined.

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.