Oracle Scheduler Job Monitoring with DBA_SCHEDULER_RUNNING_JOBS

calendar Jun 9, 2026 / · 7 min read · Oracle Scheduler DBA_SCHEDULER_RUNNING_JOBS DBA_SCHEDULER_JOBS Job Monitoring Oracle DBA Scripts 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 the job's owner, program, and schedule, 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_action,
12       r.elapsed_time                AS running_for
13FROM   dba_scheduler_running_jobs r
14       JOIN dba_scheduler_jobs j
15         ON  j.owner    = r.owner
16         AND j.job_name = r.job_name
17ORDER  BY r.elapsed_time DESC;

Code Breakdown

  • dba_scheduler_running_jobs r — the driving view. It holds one row per job that is currently executing under the Scheduler. 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 job slave process (the j00n background process) assigned to run the job. Useful when correlating with operating-system process activity.
  • The join to dba_scheduler_jobs j — brings in job_action (the PL/SQL block, stored procedure, or executable the job runs). The running-jobs view tells you that a job runs; the jobs view tells you what it does.
  • 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 records the actual run duration of every completed execution.
  • 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.
  • Lightweight jobs created with the older DBMS_JOB interface do not appear here; they surface in DBA_JOBS_RUNNING instead. Modern databases should be on the Scheduler, but legacy jobs can hide in the old view.

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. A job stuck on enq: TM - contention is blocked by a lock, not by its own logic — cancelling it may not be the right fix, because the real culprit is whatever holds the lock.

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

The Scheduler runs jobs through a pool of slave processes sized 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 tells you whether the pool 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 DISABLED or BROKEN job will never start. 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. Insufficient JOB_QUEUE_PROCESSES can also cause jobs to start late rather than run long — compare the running count against that parameter before concluding a job is hung.

References

Posts in this series