Database Job Definitions (djob.sql)

What This Script Does

This script displays comprehensive information about database jobs scheduled through DBMS_JOB, including their execution history, current status, scheduling intervals, and failure counts. It helps DBAs monitor and troubleshoot traditional Oracle job scheduling (Note: For newer implementations, consider DBMS_SCHEDULER).

Script

rem  djob.sql
rem
ttitle 'Job Definitions'
rem
set lines 132
rem
col instance format 999 heading 'INST'
col job format 999 heading 'JOB'
col log_user format a8 heading 'LOG|USER'
col priv_user format a8 heading 'PRIV|USER'
col schema_user format a8 heading 'SCHEMA|USER'
col last_date format a9
col last_sec format a8
col this_date format a9
col this_sec format a8
col next_date format a9
col next_sec format a8
col total_time format 99999 heading 'TOTAL|TIME'
col broken format a1 heading 'B'
col interval format a20 heading 'INTERVAL'
col failures format 999 heading 'FAIL'
col what format a20 heading 'WHAT'
rem
select instance, job, log_user, priv_user, schema_user,
       last_date, last_sec,
       this_date, this_sec,
       next_date, next_sec,
       total_time, broken, interval,
       failures, what
  from sys.dba_jobs
 order by instance, job;

Usage

-- Run the script in SQL*Plus or SQLcl
@djob.sql

-- No parameters required - displays all jobs

Required Privileges

SELECT on DBA_JOBS
Typically requires DBA role

Sample Output

Job Definitions

      LOG      PRIV     SCHEMA                                           TOTAL
INST  JOB USER     USER     USER     LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC  TIME B INTERVAL             FAIL WHAT
---- ---- -------- -------- -------- --------- -------- --------- -------- --------- -------- ------ - -------------------- ---- --------------------
   1   21 SYSTEM   SYSTEM   HR       06-JAN-24 03:00:02           06-JAN-24 04:00:00   1245 N SYSDATE+1/24           0 GATHER_STATS_JOB;
   1   22 SYSTEM   SYSTEM   PAYROLL  06-JAN-24 02:30:15           06-JAN-24 14:30:00    567 N SYSDATE+1/2            0 PROCESS_PAYROLL;
   1   23 MONITOR  MONITOR  MONITOR  06-JAN-24 03:45:30           06-JAN-24 03:50:00     89 N SYSDATE+5/1440         0 CHECK_TABLESPACE;
   1   24 BACKUP   BACKUP   BACKUP   05-JAN-24 22:00:45                                     Y TRUNC(SYSDATE)+22/24   3 BACKUP_DATABASE;
   2   31 SYSTEM   SYSTEM   APP      06-JAN-24 00:00:10           07-JAN-24 00:00:00   3456 N TRUNC(SYSDATE+1)       0 DAILY_CLEANUP;

Key Output Columns

INST - Instance number (for RAC environments)
JOB - Job number (unique identifier)
LOG USER - User who submitted the job
PRIV USER - User whose privileges run the job
SCHEMA USER - Schema where job executes
LAST_DATE/SEC - Last successful execution
THIS_DATE/SEC - Current execution (if running)
NEXT_DATE/SEC - Next scheduled execution
TOTAL TIME - Total execution time (seconds)
B - Broken flag (Y=broken, N=normal)
INTERVAL - Scheduling expression
FAIL - Number of consecutive failures
WHAT - Job action/procedure

Understanding Job Status

Broken Jobs

B = ‘Y’ - Job is broken (won’t run)
Usually due to repeated failures
Requires manual intervention to fix

Failure Tracking

FAIL > 0 - Job has failed consecutively
After 16 failures, job becomes broken
Check alert log for error details

Scheduling Intervals

SYSDATE+1 - Daily
SYSDATE+1/24 - Hourly
SYSDATE+1/1440 - Every minute
TRUNC(SYSDATE+1) - Daily at midnight

Common Use Cases

Find Broken Jobs

SELECT job, schema_user, what, failures
FROM dba_jobs
WHERE broken = 'Y';

Find Running Jobs

SELECT job, schema_user, what, this_date
FROM dba_jobs
WHERE this_date IS NOT NULL;

Find Jobs Not Running on Schedule

SELECT job, what, next_date,
       ROUND((SYSDATE - next_date) * 24) hours_overdue
FROM dba_jobs
WHERE broken = 'N'
AND next_date < SYSDATE;

Troubleshooting Jobs

Fix Broken Jobs

-- Mark job as not broken
BEGIN
   DBMS_JOB.BROKEN(job => 24, broken => FALSE);
   COMMIT;
END;
/

Force Job Execution

-- Run job immediately
BEGIN
   DBMS_JOB.RUN(job => 24);
END;
/

Change Job Interval

-- Change to run every 2 hours
BEGIN
   DBMS_JOB.INTERVAL(job => 21,
                     interval => 'SYSDATE+2/24');
   COMMIT;
END;
/

Remove Job

-- Delete a job
BEGIN
   DBMS_JOB.REMOVE(job => 24);
   COMMIT;
END;
/

Performance Considerations

Job Queue Processes

-- Check job queue processes
SHOW PARAMETER job_queue_processes

-- Increase if jobs are waiting
ALTER SYSTEM SET job_queue_processes = 10;

Long Running Jobs

-- Find jobs running over 1 hour
SELECT j.job, j.what,
       ROUND((SYSDATE - j.this_date) * 24, 2) hours_running
FROM dba_jobs j
WHERE j.this_date IS NOT NULL
AND (SYSDATE - j.this_date) * 24 > 1;

Migration to DBMS_SCHEDULER

Why Migrate

More features and flexibility
Better resource management
Enhanced monitoring capabilities
Time zone support

Check Scheduler Jobs

-- View DBMS_SCHEDULER jobs
SELECT owner, job_name, enabled, state,
       last_start_date, next_run_date
FROM dba_scheduler_jobs
ORDER BY owner, job_name;

Best Practices

Job Design

Include error handling in job procedures
Log job execution details
Set reasonable failure thresholds
Use appropriate scheduling intervals

Monitoring

Check for broken jobs daily
Monitor job execution times
Review failure patterns
Set up alerts for critical jobs

Scheduled Jobs (djobs.sql) - Enhanced job monitoring
Invalid Objects (dinvalid.md) - Check for invalid job procedures
Database Health Check (health.md) - Overall system health

Notes

DBMS_JOB is deprecated in favor of DBMS_SCHEDULER
Jobs run in the job queue slave processes
Job execution commits automatically
Time values are in database server time zone