Oracle Scheduler Job Monitoring with DBA_SCHEDULER_RUNNING_JOBS

calendar Jun 9, 2026 / · 8 min read · Oracle Scheduler Job Monitoring Oracle DBA Scripts dba_scheduler_running_jobs dba_scheduler_jobs dba_scheduler_job_run_details v$session v$session_wait dbms_scheduler  ·
Share on: linkedin copy

Oracle Scheduler Job Monitoring with DBA_SCHEDULER_RUNNING_JOBS

Purpose

Which Oracle Scheduler jobs are executing at this instant, and how long has each been running? DBA_SCHEDULER_RUNNING_JOBS answers that directly. It lists only the jobs that are active right now — not the full job catalog, not the run history, just the live set. Joining it to DBA_SCHEDULER_JOBS adds what the job runs (its inline action or its program name), so a single query turns "is anything stuck?" into a concrete list of names, session IDs, and elapsed times.

This is the audit step a DBA runs before cancelling a long-running job, before a maintenance window, or when a batch process appears to have hung. Knowing the session ID lets you trace the job to its wait events; knowing the elapsed time tells you whether the job is merely slow or genuinely stalled.

The query below is read-only and safe to run on a busy production instance. It returns nothing when no jobs are active, which is itself a clear signal that the Scheduler is idle.

Code

 1SET LINESIZE 200
 2COLUMN owner          FORMAT A18
 3COLUMN job_name       FORMAT A30
 4COLUMN running_for    FORMAT A16
 5COLUMN session_id     FORMAT 999999
 6
 7SELECT r.owner,
 8       r.job_name,
 9       r.session_id,
10       r.slave_process_id            AS slave_pid,
11       j.job_type,
12       j.job_action,
13       j.program_name,
14       r.elapsed_time                AS running_for
15FROM   dba_scheduler_running_jobs r
16       JOIN dba_scheduler_jobs j
17         ON  j.owner    = r.owner
18         AND j.job_name = r.job_name
19ORDER  BY r.elapsed_time DESC;

Code Breakdown

  • dba_scheduler_running_jobs r — the driving view. It lists the running Scheduler jobs. Oracle's column list includes JOB_SUBNAME, used when a job runs a chain step, so a chain job can appear more than once; expect repeated job names in that case. When no jobs are running, the query returns no rows, which is itself a useful answer.
  • elapsed_time — an INTERVAL DAY TO SECOND value showing how long the job has been running in the current execution. Ordering by it descending puts the longest-running job at the top, which is almost always the one you care about.
  • session_id — the SID of the database session servicing the job. This is the join key into V$SESSION and V$SESSION_WAIT when you need to see what the job is waiting on.
  • slave_process_id — the process number of the worker process running the job. Oracle's reference calls it the "worker process running the Scheduler job". Useful when correlating with operating-system process activity.
  • The join to dba_scheduler_jobs j — brings in job_type and job_action, which Oracle describes as the inline job action and its type (for example PLSQL_BLOCK, STORED_PROCEDURE, or EXECUTABLE), plus program_name. A job built on a program has no inline action, so read program_name in that case and look the program up. The running-jobs view tells you that a job runs; the jobs view tells you what it points at.
  • ORDER BY r.elapsed_time DESC — sorts so the job that has been running longest sits at the top. During an incident this is the row your eyes go to first.

Key Points

  • DBA_SCHEDULER_RUNNING_JOBS shows the DBA-wide view across all schemas. The ALL_ and USER_ equivalents restrict the result set to jobs the current user can access or owns. Use the DBA_ view when troubleshooting another schema's job.
  • The view reflects only the current run. To see whether a job normally takes this long, compare against DBA_SCHEDULER_JOB_RUN_DETAILS, which holds log run details, including RUN_DURATION, for logged runs. What gets logged depends on the job's LOGGING_LEVEL (OFF, RUNS, FAILED RUNS, or FULL), so a job with logging OFF may leave no baseline to compare against.
  • A job that appears in this view but makes no progress is a candidate for DBMS_SCHEDULER.STOP_JOB. Capture the session_id first so you can confirm the session actually ends.
  • In a RAC database, the running_instance column (available in newer releases) tells you which instance is executing the job — important because the job slave runs on one node, not the cluster as a whole.
  • elapsed_time is wall-clock time, not CPU time. A long elapsed time with little CPU usually means the job is blocked on I/O, a lock, or a remote call.
  • JOB_QUEUE_PROCESSES sets the maximum number of job workers per instance for both DBMS_JOB and DBMS_SCHEDULER jobs, so the Scheduler shares that limit with any legacy jobs on the same instance.

Insights and Best Practices

Correlate with the session before acting

A running job is just a database session with a label. Once you have the session_id, join to V$SESSION to find the current SQL and to V$SESSION_WAIT to find the wait event. V$SESSION_WAIT shows the current or last wait for each session, and its SID maps to V$SESSION.SID. If the event shows the job is waiting on a resource held by another session, cancelling the job may not be the right fix, because the real culprit is whatever holds that resource.

Distinguish slow from stuck

Order by elapsed_time and compare the top job's duration against its historical average from DBA_SCHEDULER_JOB_RUN_DETAILS. A job running at three times its normal duration is a real anomaly worth investigating; a job at 1.2 times its average during a heavy load window probably just needs to finish.

Watch the job slave pool

Job workers are capped per instance by JOB_QUEUE_PROCESSES. If the running-jobs count is consistently at that ceiling, jobs are queueing rather than starting on time. Monitoring the count over time can suggest whether the limit is large enough for your workload, or whether scheduled windows are overlapping more than you expected.

Build a lightweight alert

Wrap the query in a scheduled monitoring check that flags any job whose elapsed_time exceeds a per-job threshold. This converts an occasional manual look into a standing safety net without the overhead of a full monitoring product. Store the threshold per job rather than using one global value, since a five-minute log purge and a four-hour reorg have very different "too long" points.

Keep a record of what normal looks like

The running-jobs view tells you what is happening now, but "now" only means something against a baseline. Periodically snapshot DBA_SCHEDULER_JOB_RUN_DETAILS into a small history table — job name, run duration, and run date — so you can answer "is this run unusual?" with data instead of memory. A job that has crept from twenty minutes to ninety over six weeks is a slow regression you will only catch if you kept the history; the live view alone would show each individual run as merely "long today."

Treat a missing job as a finding, not a non-event

When you run this check during an incident and the job you expected to be active is simply absent, that absence is information. A nightly batch that should be running at 03:00 but does not appear in the view has either already finished, never started, or failed to schedule. Cross-check DBA_SCHEDULER_JOBS.next_run_date and the last entry in DBA_SCHEDULER_JOB_RUN_DETAILS to tell those three cases apart before assuming the job is fine.

When to Run This

  • Before a planned maintenance window, to confirm no long jobs are mid-flight.
  • When a nightly batch is reported "still running" the next morning.
  • Before issuing DBMS_SCHEDULER.STOP_JOB, to capture the session ID and current state.
  • During performance incidents, to rule a Scheduler job in or out as the source of load.
  • As a periodic automated check that alerts on jobs exceeding expected runtimes.

Troubleshooting Common Issues

If the query returns no rows, no Scheduler jobs are currently executing — that is normal between scheduled runs and is not an error. If a job you expect to be running is absent, check DBA_SCHEDULER_JOBS.state; a job in the DISABLED or BROKEN state is not expected to run until it is fixed. If a job appears here but never completes, join its session_id to V$SESSION_WAIT to find the blocking event, and check DBA_SCHEDULER_JOB_RUN_DETAILS after it ends for the failure status and any error number. A JOB_QUEUE_PROCESSES value that is too low may also make jobs start late rather than run long, so compare the running count against that parameter before concluding a job is hung. Also check DBA_SCHEDULER_JOBS.state: Oracle lists values such as BROKEN, DISABLED, FAILED, SCHEDULED, and RETRY SCHEDULED, and each points to a different next step.

References

Posts in this series