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:

Other People Are Reading

Things you need

  • Oracle 11g Databaase
  • SYSDBA permissions

Show MoreHide


  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:



    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');



  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:


    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:
  • 'example_job', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
  • END;
  • /

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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