Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 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.


Fo
r 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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.