Review Job Run History with DBA_SCHEDULER_JOB_RUN_DETAILS

calendar Aug 26, 2026 / · 9 min read · Oracle DBA DBA_SCHEDULER_JOB_RUN_DETAILS Oracle Scheduler Job Run History RUN_DURATION Failed Jobs Oracle Administration Scheduler Job Monitoring Oracle Administration Jobs  ·
Share on: linkedin copy

Review Job Run History with DBA_SCHEDULER_JOB_RUN_DETAILS

Purpose

A stuck job that fails silently overnight rarely announces itself. The next morning's dependent process just doesn't have the data it expected, and by then the only trace of what actually happened lives in the Scheduler's own run log. DBA_SCHEDULER_JOB_RUN_DETAILS is that run log — a record of every completed execution the Scheduler has kept, covering every job in the database rather than only the current user's own jobs. It is a different question from "what is running right now": that question belongs to DBA_SCHEDULER_RUNNING_JOBS, a live snapshot of in-flight jobs that disappears the moment a job finishes. DBA_SCHEDULER_JOB_RUN_DETAILS is the after-the-fact history, and it is the only place STATUS, ERROR#, ACTUAL_START_DATE, and RUN_DURATION can be reviewed together for a run that has already ended.

Each row in the view corresponds to one completed run of one job, and the four columns above answer the four questions a DBA actually has after a batch window: did the run succeed, what error number came back if it didn't, exactly when did it start, and how long did it take. Reviewed in isolation, any one of those columns is a fact about a single run. Reviewed together across a job's full history, they turn into a trend — a job that used to finish in four minutes and now takes eleven, or a job that has failed with the same ERROR# on the last three attempts.

This post covers the column set the view exposes, how that set differs between an older Oracle release and a current one, how to isolate failed runs and duration drift with targeted queries, and where the log-management side of the view — purging old entries — fits into a regular review routine.

Code

 1-- Step 1: full run history for one named job, most recent first
 2SELECT log_id,
 3       log_date,
 4       job_name,
 5       status,
 6       error#,
 7       actual_start_date,
 8       run_duration
 9FROM   dba_scheduler_job_run_details
10WHERE  job_name = 'GATHER_STATS_JOB'
11ORDER  BY log_date DESC;
12
13-- Step 2: find every run across all jobs that recorded an error
14SELECT job_name,
15       job_subname,
16       log_date,
17       status,
18       error#,
19       run_duration
20FROM   dba_scheduler_job_run_details
21WHERE  error# <> 0
22ORDER  BY log_date DESC;
23
24-- Step 3: track duration drift for one job across its history, oldest first
25SELECT job_name,
26       actual_start_date,
27       run_duration
28FROM   dba_scheduler_job_run_details
29WHERE  job_name = 'GATHER_STATS_JOB'
30ORDER  BY actual_start_date;
31
32-- Step 4: pull the recorded error and output text for a specific failed run
33SELECT log_id,
34       job_name,
35       error#,
36       errors,
37       output,
38       additional_info
39FROM   dba_scheduler_job_run_details
40WHERE  job_name = 'GATHER_STATS_JOB'
41AND    error# <> 0
42ORDER  BY log_date DESC;
43
44-- Step 5: the in-flight counterpart -- what is running right now, not what already ran
45SELECT * FROM all_scheduler_running_jobs;
46
47-- Step 6: purge the run history once it has been reviewed
48EXEC DBMS_SCHEDULER.purge_log;

Code Breakdown

Step 1: full history for a named job

LOG_ID is the unique identifier of the log entry, and LOG_DATE records when that entry was written. JOB_NAME and the view's other identifying columns scope the row to a single job; ordering by LOG_DATE DESC puts the most recent run at the top, the natural starting point when checking whether last night's job ran at all.

Step 2: isolating failed runs with ERROR#

ERROR# is documented as carrying "the error number in the case of an error" — meaning it is the reliable numeric signal that something went wrong, independent of how the STATUS column happens to phrase the outcome in a given release. Filtering with error# <> 0 finds every run across every job that recorded a failure, without needing to know or match a specific status string. JOB_SUBNAME is included here because it is populated "for a chain step job" — a single chain execution can produce multiple rows in this view, one per step, and JOB_SUBNAME is what distinguishes them.

Step 3: duration drift, oldest first

The same job, same two columns as Step 1 — ACTUAL_START_DATE and RUN_DURATION — but ordered ascending instead of descending. Read top to bottom, this turns a list of individual run durations into a visible trend: a RUN_DURATION climbing steadily across dozens of rows is a workload-growth signal long before it becomes an incident.

Step 4: reading the recorded error and output text

Once ERROR# has flagged a run, ERRORS and OUTPUT hold the actual message text — described as "error messages generated by this job run" and "output messages generated by this job run" respectively. BINARY_ERRORS and BINARY_OUTPUT exist as BLOB columns alongside them for cases where the text exceeds what the VARCHAR2(4000) columns can hold. ADDITIONAL_INFO carries further context on the run when applicable, and is the same column referenced for troubleshooting job execution in Oracle's own scheduling documentation.

Step 5: the in-flight counterpart

DBA_SCHEDULER_JOB_RUN_DETAILS only ever shows a run after it has finished. ALL_SCHEDULER_RUNNING_JOBS, by contrast, is queried to "check progress of all running jobs" while they are still executing. The two views answer different questions at different points in a job's lifecycle, and neither one substitutes for the other.

Step 6: purging old log entries

Entries in DBA_SCHEDULER_JOB_RUN_DETAILS accumulate over time and can be purged with DBMS_SCHEDULER.PURGE_LOG. Run this after a review pass, not before — purging first removes the very history a duration-drift or failure review depends on.

Key Points

  • ERROR# is the reliable filter for failed runs. It carries a nonzero value specifically "in the case of an error," which makes it a more consistent filter target than matching on STATUS text.
  • DBA_SCHEDULER_JOB_RUN_DETAILS is completed-run history only. A job still executing has no row here yet; that job shows up in DBA_SCHEDULER_RUNNING_JOBS instead.
  • ALL_, DBA_, and USER_ versions of the view scope differently. ALL_SCHEDULER_JOB_RUN_DETAILS shows jobs accessible to the current user, DBA_SCHEDULER_JOB_RUN_DETAILS shows every job in the database, and USER_SCHEDULER_JOB_RUN_DETAILS shows only jobs the current user owns.
  • The column set has grown across Oracle releases. Oracle Database 10.2's version of the view carries LOG_ID, LOG_DATE, OWNER, JOB_NAME, STATUS, ERROR#, REQ_START_DATE, ACTUAL_START_DATE, RUN_DURATION, INSTANCE_ID, SESSION_ID, SLAVE_PID, CPU_USED, and ADDITIONAL_INFO — with CPU_USED typed as a plain NUMBER. A current release adds JOB_SUBNAME, CREDENTIAL_OWNER, CREDENTIAL_NAME, DESTINATION_OWNER, DESTINATION, ERRORS, OUTPUT, BINARY_ERRORS, and BINARY_OUTPUT, and CPU_USED is retyped as an INTERVAL DAY(3) TO SECOND(2) — a script written against the older column set will not read CPU_USED correctly on a current release.
  • CREDENTIAL_OWNER, CREDENTIAL_NAME, DESTINATION_OWNER, and DESTINATION are the remote-job columns. They record the credential and destination used "for this remote job run" — relevant when a job runs on a remote database or external host rather than locally.
  • JOB_SUBNAME separates chain-step rows. A chain job's individual steps each write their own row, distinguished by JOB_SUBNAME rather than by JOB_NAME alone.

Insights and Best Practices

Read STATUS and ERROR# as a pair, not STATUS alone

STATUS records the outcome of the job run, but ERROR# is the column explicitly documented as carrying a value when something went wrong. Building a failure-review query around error# <> 0 rather than a specific STATUS string keeps the query stable regardless of exactly how a given release or job type phrases the status text.

Track duration as a series, not a single number

A single RUN_DURATION value tells you how long one run took. The same column pulled across a job's full history, ordered by ACTUAL_START_DATE, tells you whether that duration is stable, growing, or spiking on a particular day of the week. The second read is the one that catches a slow-motion capacity problem before it becomes a missed batch window.

Version-check before writing a CPU_USED script

Because CPU_USED changed datatype from NUMBER in Oracle Database 10.2 to INTERVAL DAY(3) TO SECOND(2) in current releases, a script that assumes the older numeric type will either fail or misinterpret the value on a newer database. Confirming the target release's actual column list — via DESCRIBE dba_scheduler_job_run_details or the version-appropriate reference documentation — before relying on CPU_USED avoids that mismatch.

Don't confuse run history with live monitoring

DBA_SCHEDULER_JOB_RUN_DETAILS and ALL_SCHEDULER_RUNNING_JOBS cover adjacent but non-overlapping parts of a job's lifecycle. A monitoring script checking "is anything broken" needs the run-details view; a script checking "is anything hung right now" needs the running-jobs view. Building one review routine that queries both, rather than assuming either one alone gives the full picture, closes the gap between a job that's late and a job that's already failed.

Purge on a schedule, after review

Since old entries can be removed with DBMS_SCHEDULER.PURGE_LOG, it is worth deciding a review cadence — daily or weekly — and purging only after that review has run, rather than letting the log grow indefinitely or purging before anyone has looked at it.

When to Use This Check

  • Auditing overnight or weekend batch jobs each morning for failures that need attention before the business day starts.
  • Confirming whether a job's RUN_DURATION is trending upward over weeks, ahead of it becoming a missed maintenance window.
  • Diagnosing which specific step failed inside a chain job, using JOB_SUBNAME to isolate the failing step from the rest of the chain.
  • Confirming a remote job actually reached its intended destination, using the DESTINATION and CREDENTIAL_NAME columns.
  • Pulling the ERRORS and OUTPUT text for a failed run instead of re-running the job just to see what went wrong.
  • Housekeeping the Scheduler's own log once a review pass is complete.

Troubleshooting Common Issues

A job that should have a long history returns no rows. Check which view is being queried — USER_SCHEDULER_JOB_RUN_DETAILS only shows jobs owned by the connecting user, and a job created under a different schema will not appear there even though it appears in DBA_SCHEDULER_JOB_RUN_DETAILS.

RUN_DURATION is null for a job you know executed. A row only gets its full set of values once the run has actually completed. A job that is still executing has no row in this view yet — check ALL_SCHEDULER_RUNNING_JOBS instead to confirm it is genuinely still in progress rather than stuck.

CPU_USED returns an unexpected value or type error in a script ported from an older environment. Confirm the target database's actual column type for CPU_USED before reusing a script written against a different release — the column changed from a plain number to an interval type between releases.

History for a job seems to stop abruptly at some point in the past. This is usually the result of a prior DBMS_SCHEDULER.PURGE_LOG call, either scheduled or run manually, removing entries older than its retention window.

References

Posts in this series