DBA_SCHEDULER_JOBS - Monitor Oracle Scheduled Jobs

DBA_SCHEDULER_JOBS

Overview

DBA_SCHEDULER_JOBS describes every job created through Oracle’s DBMS_SCHEDULER package, which replaced the legacy DBMS_JOB mechanism in Oracle 10g. Each row represents a scheduled unit of work — a PL/SQL block, stored procedure, external script, or executable — managed by the Oracle Scheduler infrastructure. Oracle uses DBMS_SCHEDULER internally for its own maintenance tasks (statistics gathering, segment advisor, space management), so this view covers both user-defined automation and Oracle-owned jobs.

DBAs rely on DBA_SCHEDULER_JOBS as the primary dashboard for job health: checking whether jobs are running on schedule, have failed recently, are currently executing, or have been accidentally disabled. It integrates with DBA_SCHEDULER_JOB_RUN_DETAILS for per-run history and DBA_SCHEDULER_PROGRAMS for reusable program definitions.

View Type: Static Data Dictionary View Available Since: Oracle 10g Required Privileges: SELECT on DBA_SCHEDULER_JOBS, SELECT_CATALOG_ROLE, or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the job
JOB_NAME	VARCHAR2(128)	Name of the job
JOB_SUBNAME	VARCHAR2(128)	Subname for a job step within a chain; NULL for top-level jobs
JOB_STYLE	VARCHAR2(17)	REGULAR, LIGHTWEIGHT, or IN_MEMORY_RUNTIME
JOB_TYPE	VARCHAR2(16)	PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE, EXTERNAL_SCRIPT, or SQL_SCRIPT
JOB_ACTION	VARCHAR2(4000)	PL/SQL text, procedure name, or executable path that the job runs
SCHEDULE_TYPE	VARCHAR2(12)	ONCE, NAMED, CALENDAR, IMMEDIATE, or EVENT
START_DATE	TIMESTAMP WITH TIME ZONE	Earliest date and time the job will run
REPEAT_INTERVAL	VARCHAR2(4000)	DBMS_SCHEDULER calendar expression or PL/SQL expression defining recurrence
END_DATE	TIMESTAMP WITH TIME ZONE	Date after which the job will not run; NULL for no end date
ENABLED	VARCHAR2(5)	TRUE if the job is enabled; FALSE if disabled
STATE	VARCHAR2(15)	DISABLED, RETRY SCHEDULED, SCHEDULED, RUNNING, COMPLETED, BROKEN, FAILED, REMOTE, SUCCEEDED, or CHAIN_STALLED
LAST_START_DATE	TIMESTAMP WITH TIME ZONE	Date and time of the most recent execution start
LAST_RUN_DURATION	INTERVAL DAY TO SECOND	Wall-clock duration of the most recent execution
NEXT_RUN_DATE	TIMESTAMP WITH TIME ZONE	Scheduled date and time for the next execution
RUN_COUNT	NUMBER	Total number of times the job has been run since it was created
FAILURE_COUNT	NUMBER	Number of failed runs since the job was created
RETRY_COUNT	NUMBER	Number of retry attempts for the current or most recent run
MAX_FAILURES	NUMBER	Maximum allowed consecutive failures before the job is marked BROKEN; NULL for unlimited
MAX_RUNS	NUMBER	Maximum total runs before the job is automatically disabled; NULL for unlimited
COMMENTS	VARCHAR2(240)	Optional description of the job
LOGGING_LEVEL	VARCHAR2(11)	OFF, RUNS, FAILED RUNS, or FULL — controls detail level in DBA_SCHEDULER_JOB_LOG
AUTO_DROP	VARCHAR2(5)	TRUE if the job is automatically dropped after it completes or expires

Essential Queries

Basic Usage

List all user-defined enabled jobs with schedule and recent execution status:

SELECT
    owner,
    job_name,
    job_type,
    enabled,
    state,
    last_start_date,
    last_run_duration,
    next_run_date,
    run_count,
    failure_count,
    SUBSTR(job_action, 1, 80)   AS job_action
FROM
    dba_scheduler_jobs
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'ORACLE_OCM', 'XDB')
ORDER BY
    owner,
    job_name;

Monitoring Query

Find all failed or broken jobs, along with jobs whose failure count has increased — the first-line check in a scheduler health review:

SELECT
    owner,
    job_name,
    enabled,
    state,
    last_start_date,
    last_run_duration,
    run_count,
    failure_count,
    retry_count,
    next_run_date,
    SUBSTR(job_action, 1, 100)  AS job_action
FROM
    dba_scheduler_jobs
WHERE
    state IN ('FAILED', 'BROKEN', 'CHAIN_STALLED')
    OR failure_count > 0
ORDER BY
    failure_count DESC,
    state;

Combined with Other Views

Join DBA_SCHEDULER_JOBS with DBA_SCHEDULER_JOB_RUN_DETAILS to show the last 5 run outcomes per job, including error codes:

SELECT
    j.owner,
    j.job_name,
    j.state,
    r.log_date,
    r.status,
    r.run_duration,
    r.error#,
    r.additional_info
FROM
    dba_scheduler_jobs          j
    JOIN dba_scheduler_job_run_details r ON r.owner    = j.owner
                                        AND r.job_name = j.job_name
WHERE
    j.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
    AND r.log_date >= SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY
    j.owner,
    j.job_name,
    r.log_date DESC;

Currently Running Jobs

Show all jobs in the RUNNING state with their duration so far, useful for detecting hung or runaway jobs:

SELECT
    j.owner,
    j.job_name,
    j.state,
    j.last_start_date,
    ROUND(
        EXTRACT(HOUR   FROM (SYSTIMESTAMP - j.last_start_date)) * 60
      + EXTRACT(MINUTE FROM (SYSTIMESTAMP - j.last_start_date))
      + EXTRACT(SECOND FROM (SYSTIMESTAMP - j.last_start_date)) / 60,
        1
    )                           AS mins_running,
    j.last_run_duration,
    j.job_type,
    SUBSTR(j.job_action, 1, 100) AS job_action
FROM
    dba_scheduler_jobs j
WHERE
    j.state = 'RUNNING'
ORDER BY
    mins_running DESC;

Overdue Job Detection

Identify jobs that are enabled but overdue — NEXT_RUN_DATE is in the past and the job is not currently running:

SELECT
    owner,
    job_name,
    enabled,
    state,
    next_run_date,
    ROUND(
        (CAST(SYSTIMESTAMP AS DATE) - CAST(next_run_date AS DATE)) * 1440,
        1
    )                           AS mins_overdue,
    last_start_date,
    failure_count,
    repeat_interval
FROM
    dba_scheduler_jobs
WHERE
    enabled      = 'TRUE'
    AND state    != 'RUNNING'
    AND next_run_date < SYSTIMESTAMP
    AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
ORDER BY
    mins_overdue DESC;

Common Use Cases

• Nightly batch monitoring — Check STATE and LAST_RUN_DURATION each morning to confirm that batch jobs completed successfully within their expected time windows
• Failure count alerting — Set up monitoring scripts that alert when FAILURE_COUNT increases between checks, catching problems before they reach MAX_FAILURES and cause the job to break
• Disabled job audit — Filter ENABLED = ‘FALSE’ to find jobs that may have been accidentally disabled during maintenance and not re-enabled
• Running time trend analysis — Compare LAST_RUN_DURATION over time using DBA_SCHEDULER_JOB_RUN_DETAILS to detect jobs that are gradually slowing down, indicating growing data volumes or degrading plans
• Oracle internal job review — Query jobs where OWNER IN (‘SYS’, ‘SYSTEM’) to see Oracle’s own maintenance jobs (statistics gathering, segment advisor, AWR snapshots) and confirm they are running on schedule
• Scheduler infrastructure verification — After a database restart, confirm that all expected jobs have transitioned from DISABLED or SCHEDULED to RUNNING at their correct NEXT_RUN_DATE

Related Views

• DBA_JOBS — Legacy DBMS_JOB view; many production databases still have jobs in both views during migration
• V$SESSION — Find the Oracle session associated with a currently running scheduler job; join via MODULE or ACTION set by the scheduler
• DBA_SCHEDULER_JOB_RUN_DETAILS — Per-run history with status, duration, error codes, and log output; the most detailed source for failure diagnosis
• DBA_SCHEDULER_JOB_LOG — Summary log of job executions; less detail than JOB_RUN_DETAILS but faster to query for high-frequency jobs
• DBA_SCHEDULER_PROGRAMS — Reusable program definitions referenced by jobs; shows the full JOB_ACTION and argument list for program-based jobs
• DBA_SCHEDULER_CHAINS — Job chain definitions; relevant when STATE = ‘CHAIN_STALLED’

Version Notes

• Oracle 10g: DBA_SCHEDULER_JOBS introduced alongside DBMS_SCHEDULER; basic PL/SQL and stored procedure job types supported
• Oracle 11g: External script jobs (EXECUTABLE, EXTERNAL_SCRIPT types) added; credential-based execution for OS commands; event-based scheduling via SCHEDULE_TYPE = ‘EVENT’
• Oracle 12c: Lightweight jobs (JOB_STYLE = ‘LIGHTWEIGHT’) and in-memory runtime jobs introduced for very high-frequency scheduling; CON_ID column for CDB/PDB distinction
• Oracle 19c: Automatic in-memory scheduler improvements; Oracle-owned maintenance jobs (AUTO TASK) increasingly use DBMS_SCHEDULER internally; no structural changes to the view
• Oracle 23ai: No structural changes; SQL Firewall and other 23ai security features generate their own scheduler jobs visible in DBA_SCHEDULER_JOBS under the SYS schema