Scops
Docs Download

Oracle problems

Scheduler Job Failed, Broken or Stuck: What the Log Says, and the Fix

3 November 2026

A Scheduler job fails quietly. The statistics job, the partition maintenance, the nightly load or the purge raises an error at three in the morning, the Scheduler writes it to a log view that nobody opens, and the first sign is a report with yesterday's numbers, a tablespace that filled because the purge did not run, or a job that is BROKEN after failing as many times as it was allowed. The log has the error, the run that failed, the step of a chain that stalled and the job that has been running for nine hours, and the queries below read it in that order.

Which jobs are in trouble

SELECT owner, job_name, state, enabled, failure_count, run_count,
       last_start_date, next_run_date
FROM   dba_scheduler_jobs
WHERE  state IN ('BROKEN', 'FAILED', 'CHAIN_STALLED',
                 'RETRY SCHEDULED')
OR     failure_count > 0
OR     (enabled = 'TRUE' AND next_run_date < SYSTIMESTAMP)
ORDER  BY last_start_date DESC NULLS LAST;

Oracle's reference lists the states a job can be in: SCHEDULED, RUNNING, SUCCEEDED, FAILED, RETRY SCHEDULED, BROKEN, CHAIN_STALLED, STOPPED, DISABLED, COMPLETED, BLOCKED, RESOURCE_UNAVAILABLE, REMOTE and SOME FAILED. FAILURE_COUNT is the number of times the job has failed to run, and MAX_FAILURES is the number of times it will be allowed to fail before being marked broken. A job that is enabled with a NEXT_RUN_DATE in the past is overdue: either nothing is running jobs, or the previous run has not ended.

What the failed run said

SELECT log_date, owner, job_name, status, error#, run_duration,
       additional_info, errors
FROM   dba_scheduler_job_run_details
WHERE  status <> 'SUCCEEDED'
AND    log_date > SYSTIMESTAMP - INTERVAL '7' DAY
ORDER  BY log_date DESC;

ERROR# is the Oracle error number, and ERRORS holds the error stack with the line in the procedure that raised it; the two answer most failures without a second query. RUN_DURATION against the earlier rows says whether the job was already slowing down before it failed, and ADDITIONAL_INFO carries the detail the Scheduler had, such as the step of a chain.

What is running, and for how long

SELECT r.owner, r.job_name, r.elapsed_time, r.cpu_used,
       r.session_id, r.running_instance, s.sql_id, s.event
FROM   dba_scheduler_running_jobs r
LEFT   JOIN v$session s ON s.sid = r.session_id
ORDER  BY r.elapsed_time DESC;

A job with hours of ELAPSED_TIME and seconds of CPU_USED is waiting, and EVENT says on what: a lock held by another session is the usual answer, and the root blocker query finds who holds it. In RAC, join GV$SESSION on RUNNING_INSTANCE as well.

The step of a chain that stalled

SELECT owner, job_name, chain_name, step_name, state,
       error_code, start_date, end_date
FROM   dba_scheduler_running_chains
ORDER  BY owner, job_name, step_name;

Oracle's reference lists the states of a running chain step: NOT_STARTED, RUNNING, SUCCEEDED, STOPPED, FAILED, SCHEDULED, RETRY SCHEDULED, PAUSED and STALLED. A chain is stalled when no step is running or scheduled and no rule ends the chain, which is nearly always a step that failed with no rule written for its failure.

The legacy jobs

SELECT job, schema_user, broken, failures, last_date, next_date, what
FROM   dba_jobs
WHERE  broken = 'Y'
OR     failures > 0
OR     next_date < SYSDATE
ORDER  BY next_date;

DBA_JOBS still lists the DBMS_JOB jobs that older applications create. FAILURES counts the failures since the last success, and with BROKEN at Y no attempt is made to run the job at all. One parameter stops both kinds of job at once: with JOB_QUEUE_PROCESSES at 0, Oracle's reference says, DBMS_JOB jobs and Scheduler jobs will not run.

The fix, in order

  1. Read the error before touching the job. ERROR# and ERRORS name the cause, in the procedure, the data or the space, and a job re-enabled without the cause fixed is broken again by morning. An invalid procedure, a full tablespace and a snapshot too old are the three usual ones.
  2. Re-enable a broken job after the fix, and test it by hand. A job goes BROKEN after MAX_FAILURES failures. DBMS_SCHEDULER.ENABLE puts it back on its schedule and resets the counts, and DBMS_SCHEDULER.RUN_JOB runs it once outside the schedule; run in the current session, the job's error comes back to your own session instead of the log.
  3. Unstall the chain, then write the missing rule. DBMS_SCHEDULER.ALTER_RUNNING_CHAIN sets the state of the stuck step so the chain can continue or end, and EVALUATE_RUNNING_CHAIN makes the Scheduler look at the rules again. The lasting fix is a rule for every outcome of every step, including failure.
  4. Stop a job that waits, and fix what it waits on. DBMS_SCHEDULER.STOP_JOB ends the run, with FORCE if it will not stop; the lock it waited on is found with the root blocker query, and the next run goes at a time when the blocker is not there. The MAX_RUN_DURATION attribute raises an event when a run exceeds it, so the next nine-hour run is noticed in the first hour.
  5. Make an overdue job run. A job is disabled when created and must be enabled with DBMS_SCHEDULER.ENABLE; a restored or cloned database brings its jobs with it, enabled as they were; and JOB_QUEUE_PROCESSES at 0 stops every job in the database. The window the job runs in, its job class and its resource plan are the next things to check.
  6. Mend the legacy jobs too. DBMS_JOB.BROKEN(job, FALSE) puts a broken job back and DBMS_JOB.RUN runs it now; Oracle marks a legacy job broken on its own after 16 failures in a row. Moving the job to DBMS_SCHEDULER gets it a log with the error, which DBMS_JOB never had.

See it coming

Radar Engine, the detection layer of Scops, reads the job log of every database each hour: Scheduler jobs and legacy jobs, failed, broken, stalled and overdue, and the time since the last success of each, so a job that has failed twice tonight is a finding in the morning and a job that is broken is a Critical, with the error from the log in the finding:

DetectorClockWhen it fires
Job, one finding per jobHourly. Failed, broken, stalled or overdue scheduler and legacy jobs.Failing: Warning 6 hours after the last success, Critical after 24. Broken, or a stalled chain: Critical. Overdue: Watch, one day: Warning.

The finding names the job, its owner, the last success, the error of the last run and the step of a chain that stalled. Scops reads the log views through a read-only account, with nothing installed in the database and no Diagnostics Pack, on Standard Edition 2 as on Enterprise Edition.

Questions

Why is my Scheduler job BROKEN?
It failed as many times as MAX_FAILURES allows, and the Scheduler stopped scheduling it. Fix the cause from the error in DBA_SCHEDULER_JOB_RUN_DETAILS, then DBMS_SCHEDULER.ENABLE puts it back on its schedule and resets the failure count.

Where is the error of a failed Scheduler job?
In DBA_SCHEDULER_JOB_RUN_DETAILS: ERROR# is the Oracle error number, ERRORS the error stack with the line that raised it, and ADDITIONAL_INFO whatever else the Scheduler recorded, such as the chain step.

Why does my job never run at all?
A job is disabled when created and must be enabled; JOB_QUEUE_PROCESSES at 0 stops every job in the database; and a job whose previous run has not ended cannot start the next one. DBA_SCHEDULER_JOBS shows ENABLED, STATE and NEXT_RUN_DATE.

What is a stalled chain?
A chain with no step running or scheduled and no rule that ends it, which the job state shows as CHAIN_STALLED and the step state as STALLED. DBMS_SCHEDULER.ALTER_RUNNING_CHAIN moves it on; a rule for every outcome of every step keeps it from stalling again.

Related problems

Sources