Remote DBA Plans
Remote DBA Service

Oracle Scheduler Job Logs
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

There are three levels of logging associated with scheduled jobs. They are noted below along with the appropriate constants defined in the dbms_scheduler package:

logging_off - No logging
logging_runs - Only run events are logged
logging_full - All events that happen to a job during its lifetime are logged

The logging level of a job is typically set by associating it to a job class with the appropriate logging level. Since the default logging level for a job class is logging_runs and all jobs are associated with a job class, the default logging level for a job is logging_runs.

Alternatively, the logging_level parameter of a job can be set directly using the set_attribute procedure, as shown below.

BEGIN

DBMS_SCHEDULER.set_attribute (

name => 'test_log_job',

attribute => 'logging_level',

value => DBMS_SCHEDULER.logging_off);

END;

For security reasons, this method cannot change the logging level to a value lower than that of its associated class. For example, if the job’s associated job class has a logging level of logging_runs, the set_attribute procedure could only be used to switch the job’s logging level to logging_full and back to logging_runs. By doing so, administrators of the scheduler can dictate a minimum level of auditing for job execution.

The job_log_lifecycle.sql script creates, updates, enables and drops a job, thereby effectively producing a full lifecycle of events in the job log.

job_log_lifecycle.sql

BEGIN

-- Remove all logs for this job.

DBMS_SCHEDULER.purge_log(job_name => ‘test_log_job’);

-- Create job class with full logging.

DBMS_SCHEDULER.create_job_class (

job_class_name => 'test_logging_class',

resource_consumer_group => 'default_consumer_group',

logging_level => DBMS_SCHEDULER.logging_full);

-- Create job links to previous job class.

DBMS_SCHEDULER.create_job (

job_name => 'test_log_job',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN NULL; END;',

job_class => 'test_logging_class',

enabled => FALSE,

auto_drop => FALSE,

comments => 'Job used to job logs.');

-- Update the job.

DBMS_SCHEDULER.set_attribute (

name => 'test_log_job',

attribute => 'start_date',

value => SYSTIMESTAMP);

-- Enable the job.

DBMS_SCHEDULER.enable (name => 'test_log_job');

-- Pause to let the job run.

DBMS_LOCK.sleep(30);

-- Drop the job.

DBMS_SCHEDULER.drop_job (job_name => 'test_log_job');

-- Drop the job class.

DBMS_SCHEDULER.drop_job_class (job_class_name => 'test_logging_class');v

END;

This script clears down any log information associated with the job it creates, allowing it to be run multiple times with the same result.

The job_logs.sql script uses the dba_scheduler_job_log view to display log information for a specific job or all jobs.

job_logs.sql

-- *************************************************

-- Parameters:

-- 1) Specific job name or ‘all’ jobs.

-- *****************************************************************

set feedback off

alter session set nls_timestamp_tz_format='DD-MON-YYYY HH24:MI:SS.ff';

set feedback on

column owner format a10

column job_name format a30

column operation format a10

column status format a10

column log_date format a27

select

owner,

job_name,

operation,

status,

log_date

from

dba_scheduler_job_log

where

job_name = decode(upper('&1'), 'ALL', job_name, upper('&1'))

order by

log_date;

Using the previous two scripts, the sort of logging one would expect for a job with full logging enabled can be seen.

SQL> @ job_log_lifecycle.sql

PL/SQL procedure successfully completed.

SQL> @ job_logs.sql test_log_job

OWNER JOB_NAME OPERATION STATUS LOG_DATE

---------- -------------- ---------- ---------- ---------------------------

JOB_USER TEST_LOG_JOB CREATE 21-AUG-2004 15:21:23.795000

JOB_USER TEST_LOG_JOB UPDATE 21-AUG-2004 15:21:23.811000

JOB_USER TEST_LOG_JOB ENABLE 21-AUG-2004 15:21:23.827000

JOB_USER TEST_LOG_JOB RUN SUCCEEDED 21-AUG-2004 15:21:23.874000

JOB_USER TEST_LOG_JOB SUCCEEDED 21-AUG-2004 15:21:23.874000

JOB_USER TEST_LOG_JOB DROP 21-AUG-2004 15:21:54.577000

The job logs provide only top-level information about the jobs. Further details are logged in the job run details log which is covered in the next section.

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.