Skip to content
Oracle DBA Scripts & Database Utilities

Scheduled Jobs Status (djobs.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a comprehensive view of scheduled jobs in the database, showing their status, next run time, and repeat intervals.

The Script

Section titled “The Script”
rem  djobs.sql
rem
ttitle 'Oracle Scheduler Jobs Status'
rem
set lines 250
set pagesize 50
rem
clear col
col owner format a15 heading 'OWNER'
col job_name format a50 heading 'JOB_NAME'
col enabled format a10 heading 'ENABLED'
col next_run_date format a55 heading 'NEXT_RUN_DATE'
col REPEAT_INTERVAL format a75 heading 'REPEAT_INTERVAL'
rem


select
  owner
  , job_name
  , enabled
  , next_run_date
  , REPEAT_INTERVAL
from
  dba_scheduler_jobs
where
  owner like nvl(upper('&owner'),'%')
  and enabled like nvl(upper('&enabled'),'%')
  and job_name like nvl(upper('&job_name'),'%')
/

Usage

Section titled “Usage”
-- Basic usage
@djobs.sql


-- When prompted, enter:
-- owner: ** - Job owner to filter (use % for all)
-- enabled: ** - Filter by enabled status (TRUE/FALSE, use % for all)
-- job_name: ** - Job name pattern to filter (use % for all)

Parameters

Section titled “Parameters”

The script prompts for:

  • &owner - ** - Job owner to filter (use % for all)
  • &enabled - ** - Filter by enabled status (TRUE/FALSE, use % for all)
  • &job_name - ** - Job name pattern to filter (use % for all)

Required Privileges

Section titled “Required Privileges”
SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_SCHEDULER_JOBS

Sample Output

Section titled “Sample Output”
OWNER           JOB_NAME                                           ENABLED    NEXT_RUN_DATE                                           REPEAT_INTERVAL
--------------- -------------------------------------------------- ---------- ------------------------------------------------------- ---------------------------------------------------------------------------
SYS             GATHER_STATS_JOB                                   TRUE       05-JAN-25 10.00.00.000000 PM +00:00                   FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;BYHOUR=22;BYMINUTE=0;BYSECOND=0
HR              REFRESH_MATERIALIZED_VIEWS                         TRUE       06-JAN-25 02.00.00.000000 AM +00:00                   FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0
SCOTT           PURGE_OLD_DATA                                     FALSE

Key Output Columns

Section titled “Key Output Columns”
  • OWNER - Schema that owns the job
  • JOB_NAME - Name of the scheduled job
  • ENABLED - Whether the job is enabled (TRUE/FALSE)
  • NEXT_RUN_DATE - When the job will run next
  • REPEAT_INTERVAL - Scheduling frequency in Oracle calendar syntax

Common Use Cases

Section titled “Common Use Cases”

Monitor All Active Jobs

-- Check all enabled jobs
@djobs.sql
-- Enter: % for owner, TRUE for enabled, % for job_name

Check Specific Schema Jobs

-- Monitor jobs owned by HR schema
@djobs.sql
-- Enter: HR for owner, % for enabled and job_name
Section titled “Related Scripts”