Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 










Fixing Oracle broken jobs

Oracle Database Tips by Donald Burleson

Scheduled jobs appears in two places in Oracle 10g, in the dba_scheduler_jobs (for automatic jobs and jobs scheduled via dbms_scheduler) as well as in the dba_jobs views for jobs that were scheduled with the dbms_job package.

The O'Reilly book on PL/SQL Built-in Packages: notes an automatic procedure for "fixing" special types of broken jobs:

Fixing Broken Jobs Automatically

Charles Dye recommended the next example, probably based on his experiences with replication. When jobs have relatively complex execution requirements in terms of the database objects on which they depend, they can easily become broken by incurring multiple execution failures. Perhaps the DBA has modified some database links or recreated tables or views, and the job's definition has been temporarily compromised.

Well, it's a pain to manually reset the broken flag for these "not really broken" jobs, so why not have a job that regularly tries to unbreak jobs? Sounds good to me; here is a procedure called job_fixer to do just that:

/* Filename on companion disk: job5.sql */*
   || calls DBMS_JOB.BROKEN to try and set
   || any broken jobs to unbroken
   /* cursor selects user's broken jobs */
   CURSOR broken_jobs_cur
   SELECT job
     FROM user_jobs
    WHERE broken = 'Y';
   FOR job_rec IN broken_jobs_cur
END job_fixer;

The Oracle documentation suggests that fixing "disabled" jobs in 10g dbms_scheduler is different from the "broken" in dbms_job (emphasis added) and adds many "state" column values for jobs in dba_scheduler_jobs:

select state from dba_scheduler_jobs;

Job State Description
disabled The job is disabled.
scheduled The job is scheduled to be executed.
running The job is currently running.
completed The job has completed, and is not scheduled to run again.
broken The job is broken.
failed The job was scheduled to run once and failed.
retry scheduled The job has failed at least once and a retry has been scheduled to be executed.
succeeded The job was scheduled to run once and completed successfully.


Enabling Jobs

You enable jobs by using the ENABLE procedure. The effect of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by default, so you need to enable them before they can run. When a job is enabled, a validity check is performed. If the check fails, the job is not enabled.

You can enable several jobs in one call by providing a comma-delimited list of job names or job class names to the ENABLE procedure call. For example, the following statement combines jobs with job classes:

DBMS_SCHEDULER.ENABLE ('job1, job2, job3, 
   sys.jobclass1, sys.jobclass2, sys.jobclass3');

Also, note this on dbms_scheduler

Finally, Dr. Hall notes about the "state" column of dba_scheduler_jobs:

A regular failure doesn't alter the state column. We can prove this by creating a procedure that fails and scheduling it:

  RAISE_APPLICATION_ERROR(-20000, 'I forced this error!');

    job_name        => 'test1',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN proc_fail; END;',
    repeat_interval => 'freq=minutely;',
    enabled         => TRUE,
    start_date      => SYSTIMESTAMP);

If we let it run a few times we can see that the state column stays as
SCHEDULED, even after failures. You can check this using the following

SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name = 'TEST1';

As you say, you can check the details of the failures by querying the

The possible values for the STATE column vary a little, depending on the database version, but here is the state list for 10gR2.

Here is a query shows which dbms_scheduler jobs failed and why they failed:

-- What scheduled tasks failed during execution, and why?
COL log_id              FORMAT 9999   HEADING 'Log#'
COL log_date            FORMAT A32    HEADING 'Log Date'
COL owner               FORMAT A06    HEADING 'Owner'
COL job_name            FORMAT A20    HEADING 'Job'
COL status              FORMAT A10    HEADING 'Status'
COL actual_start_date   FORMAT A32    HEADING 'Actual|Start|Date'
COL error#              FORMAT 999999 HEADING 'Error|Nbr'

TTITLE 'Scheduled Tasks That Failed:'

--   job_name = 'MYJOB'

   status <> 'SUCCEEDED'
order by

Need  more details on Oracle Job Scheduling?  Get the Oracle job scheduling book.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.