How do I find errors for oracle job failures?

Written by dwayne hoover
  • Share
  • Tweet
  • Share
  • Email

The Oracle Database offers a very robust, powerful and customisable method for scheduling jobs. At a basic level, an Oracle job is a block of PL/SQL, Oracle's programming language, code that is scheduled to run at a particular time. Job schedules can be very intricate and the code run for an Oracle job can be as basic as a one line insert statement or a 10,000 line PL/SQL procedure. For the sake of this example, the specifics of the PL/SQL code being run is irrelevant as it will address determining the cause of failed Oracle jobs.

Skill level:
Easy

Other People Are Reading

Things you need

  • Oracle 11g Databaase
  • SYSDBA permissions

Show MoreHide

Instructions

  1. 1

    Use the built in package DBMS_SCHEDULER to create the job. Assuming that the desired effect is to run the procedure apps.do_something every day at 2:00PM it would look like this:

    BEGIN

    DBMS_SCHEDULER.CREATE_JOB (

    job_name => 'example_job',

    job_type => 'STORED_PROCEDURE',

    job_action => 'apps.do_something',

    start_date => '20-DEC-10 02.00.00 PM',

    repeat_interval => 'FREQ=DAILY,

    comments => 'job comments here');

    END;

    /

  2. 2

    View the job log for success or error messages. By default, Oracle logs job status in the DBA_SCHEDULER_JOB_LOG data dictionary view. Alternatively, if the job is specific to the current user, USER_SCHEDULER_JOB_LOG can be queried. An example query of this view is as follows:

    SELECT timestamp, job_name, job_class, operation, status FROM USER_SCHEDULER_JOB_LOG where job_name='example_job';

  3. 3

    View detailed information about the job. For jobs listed with the operation defined as RUN, RETRY_RUN or RECOVERY_RUN in the DBA/USER_SCHEDULED_JOB_LOG view, Oracle provides additional detail in USER_SCHEDULED_JOB_RUN_DETAILS or DBA_SCHEDULED_JOB_RUN_DETAILS. For example:

    SELECT TIMESTAMP, job_name, status,ADDITIONAL_INFO

    FROM user_scheduler_job_run_details WHERE job_name = 'example_job'

Tips and warnings

  • Setting the logging level on a job to DBMS_SCHEDULER.LOGGING_FULL creates a log entry every time the job runs and for every operation performed on a job. This can be helpful if multiple people have access to make changes to the job to track when changes were made to it. Actions like creation, enable/disable, drop and update are tracked with this logging level.
  • To activate it:
  • BEGIN
  • DBMS_SCHEDULER.SET_ATTRIBUTE (
  • 'example_job', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
  • END;
  • /

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.