DBA_JOBS - Monitor Legacy Oracle DBMS_JOB Scheduling

DBA_JOBS

Overview

DBA_JOBS describes every job submitted through the legacy DBMS_JOB package, which was the original Oracle job scheduling mechanism available from Oracle 7 through Oracle 10g. Each row represents a scheduled PL/SQL block that Oracle’s job queue processes (SNP background processes, now named J0xx) execute according to a calculated next-run interval.

While Oracle 10g introduced the far more capable DBMS_SCHEDULER (visible via DBA_SCHEDULER_JOBS), DBMS_JOB jobs remain common in long-running production databases that have not been fully migrated. DBAs need DBA_JOBS to monitor job health, diagnose broken jobs (those that have failed 16 consecutive times), check execution frequency, and plan migration to DBMS_SCHEDULER. This view is the essential starting point for legacy job management.

View Type: Static Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_JOBS, SELECT_CATALOG_ROLE, or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
JOB	NUMBER	Unique job number assigned by DBMS_JOB.SUBMIT; used for all subsequent management operations
LOG_USER	VARCHAR2(128)	User who submitted the job
PRIV_USER	VARCHAR2(128)	User whose privileges the job executes with
SCHEMA_USER	VARCHAR2(128)	Default schema for the job’s name resolution
LAST_DATE	DATE	Date and time of the last successful execution
LAST_SEC	VARCHAR2(8)	Time portion of LAST_DATE as HH24:MI:SS
THIS_DATE	DATE	Date and time the current (in-progress) execution started; NULL if not currently running
THIS_SEC	VARCHAR2(8)	Time portion of THIS_DATE as HH24:MI:SS
NEXT_DATE	DATE	Date and time of the next scheduled execution
NEXT_SEC	VARCHAR2(8)	Time portion of NEXT_DATE as HH24:MI:SS
TOTAL_TIME	NUMBER	Total wall-clock seconds spent in all executions of this job since it was submitted
BROKEN	VARCHAR2(1)	Y if the job has failed 16 consecutive times and will no longer be scheduled automatically; N otherwise
INTERVAL	VARCHAR2(200)	PL/SQL expression evaluated after each execution to compute the next NEXT_DATE
FAILURES	NUMBER	Number of consecutive failures since the last successful execution
WHAT	VARCHAR2(4000)	PL/SQL anonymous block or procedure call that the job executes
NLS_ENV	VARCHAR2(4000)	NLS parameters in effect when the job was submitted; carried into job execution
MISC_ENV	RAW(32)	Miscellaneous environment settings (internal use)
INSTANCE	NUMBER	RAC instance number the job is pinned to; 0 means any instance can run it

Essential Queries

Basic Usage

List all jobs with their schedule, status, and last execution time:

SELECT
    job,
    log_user,
    schema_user,
    last_date,
    next_date,
    broken,
    failures,
    ROUND(total_time, 2)    AS total_secs,
    interval,
    SUBSTR(what, 1, 80)     AS job_what
FROM
    dba_jobs
ORDER BY
    next_date;

Monitoring Query

Find all broken or failing jobs — the primary alert condition for legacy DBMS_JOB environments:

SELECT
    job,
    log_user,
    schema_user,
    last_date,
    next_date,
    broken,
    failures,
    SUBSTR(what, 1, 120)    AS job_what
FROM
    dba_jobs
WHERE
    broken   = 'Y'
    OR failures > 0
ORDER BY
    failures DESC,
    broken DESC;

Combined with Other Views

Join DBA_JOBS with V$SESSION to find jobs that are currently executing, along with their session details and current SQL:

SELECT
    j.job,
    j.log_user,
    j.this_date         AS started_at,
    ROUND((SYSDATE - j.this_date) * 86400, 0)  AS secs_running,
    j.what,
    s.sid,
    s.serial#,
    s.status            AS session_status,
    s.event,
    s.sql_id
FROM
    dba_jobs    j
    JOIN v$session s ON s.schemaname = j.schema_user
                     AND s.module    LIKE '%J0%'
WHERE
    j.this_date IS NOT NULL
ORDER BY
    secs_running DESC;

Jobs Not Running on Schedule

Identify jobs whose NEXT_DATE has passed but have not started, which may indicate that job queue processes are unavailable or the job is stuck:

SELECT
    job,
    log_user,
    schema_user,
    next_date,
    ROUND((SYSDATE - next_date) * 1440, 1)  AS mins_overdue,
    broken,
    failures,
    SUBSTR(what, 1, 100)                    AS job_what
FROM
    dba_jobs
WHERE
    next_date < SYSDATE
    AND this_date IS NULL
    AND broken    = 'N'
ORDER BY
    mins_overdue DESC;

Job Execution History Estimation

Estimate average and total execution time per job, using TOTAL_TIME and a calculated execution count from LAST_DATE:

SELECT
    job,
    log_user,
    schema_user,
    last_date,
    next_date,
    broken,
    failures,
    ROUND(total_time, 1)                AS total_secs,
    interval,
    SUBSTR(what, 1, 80)                 AS job_what,
    INSTANCE                            AS pinned_instance
FROM
    dba_jobs
WHERE
    log_user NOT IN ('SYS', 'SYSTEM')
ORDER BY
    total_time DESC;

Common Use Cases

• Broken job remediation — When BROKEN = ‘Y’, diagnose the error from the alert log or DBA_SCHEDULER_JOB_RUN_DETAILS equivalent, fix the underlying issue, then use DBMS_JOB.BROKEN(job, FALSE) to re-enable the job
• Failure count monitoring — Jobs with FAILURES > 0 but not yet broken are approaching automatic disablement; investigate and fix them before they reach the 16-failure threshold
• Migration to DBMS_SCHEDULER — Query DBA_JOBS and cross-reference with DBA_SCHEDULER_JOBS to identify legacy jobs that have not yet been migrated; Oracle recommends all new jobs use DBMS_SCHEDULER
• RAC instance affinity review — In RAC environments, filter INSTANCE != 0 to find jobs pinned to a specific instance, which could cause failures if that instance is down
• Overdue job alerting — Jobs with NEXT_DATE in the past and THIS_DATE IS NULL indicate scheduler queue issues; check JOB_QUEUE_PROCESSES parameter and SNP/J0xx background process status
• NLS environment audit — Inspect NLS_ENV for jobs that manipulate date or number data to confirm they will behave consistently after an NLS parameter change at the database level

Related Views

• DBA_SCHEDULER_JOBS — The modern replacement for DBA_JOBS; supports advanced scheduling, event-based triggers, job chains, and detailed run history
• DBA_JOBS_RUNNING — Shows only the subset of DBA_JOBS rows that are currently executing, without the need to filter on THIS_DATE IS NOT NULL
• V$SESSION — Join to find the Oracle session associated with a currently running job
• DBA_SCHEDULER_JOB_RUN_DETAILS — After migrating jobs to DBMS_SCHEDULER, this view provides detailed per-run history with error codes and log output

Version Notes

• Oracle 7 / 8 / 9i: DBMS_JOB was the only Oracle job scheduling mechanism; fully supported in all these releases
• Oracle 10g: DBMS_SCHEDULER and DBA_SCHEDULER_JOBS introduced as the strategic replacement; DBMS_JOB retained for backwards compatibility
• Oracle 11g: JOB_QUEUE_PROCESSES parameter controls the number of J0xx processes that service both DBMS_JOB and DBMS_SCHEDULER jobs; the two systems share the queue infrastructure
• Oracle 12c (Multitenant): DBA_JOBS scoped per container in a CDB; CON_ID column present; DBMS_JOB fully functional per PDB
• Oracle 19c / 21c: DBMS_JOB officially deprecated in documentation in favour of DBMS_SCHEDULER; continues to function but no new features added
• Oracle 23ai: DBMS_JOB continues to function; Oracle’s migration guidance recommends converting all DBMS_JOB jobs to DBMS_SCHEDULER to take advantage of improved monitoring, error handling, and scheduling options