dba_scheduler_jobs.sql

<- Scripts

-- dba_scheduler_jobs.sql
-- Purpose: Show dba_scheduler_jobs (scheduled, failed and running).

SET LINESIZE 145
SET PAGESIZE 9999

COL owner             FORMAT a15
COL job_name          FORMAT a35
COL job_class         FORMAT a15
COL state             FORMAT a12
COL enabled           FORMAT a10
COL next_run_date     FORMAT a16
COL last_start_date   FORMAT a16
COL next_run_duration FORMAT a16
COL failure_count     FORMAT 9999


prompt 
prompt +--------------------------------+
prompt | dba_scheduler_jobs: Scheduled  |
prompt +--------------------------------+

SELECT
    owner,
    job_name,
    state,
    enabled,
    TO_CHAR(last_start_date,  'YYYY-MM-DD HH24:MI') AS last_start_date,
    TO_CHAR(next_run_date,    'YYYY-MM-DD HH24:MI') AS next_run_date,    
    failure_count
FROM
    DBA_SCHEDULER_JOBS
WHERE enabled = 'TRUE'     
ORDER BY owner,job_name;


prompt 
prompt +------------------------------------------+
prompt | dba_scheduler_jobs: Failed (in last day) |
prompt +------------------------------------------+
SELECT
    job_name,
    status,
    TO_CHAR(actual_start_date, 'YYYY-MM-DD HH24:MI') AS start_date,
    TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI') AS log_date,
    run_duration,
    error#,
    additional_info
FROM
    DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE
    status = 'FAILED'
    AND log_date >= SYSDATE - 1
ORDER BY
    log_date DESC;

prompt 
prompt +--------------------------------+
prompt | dba_scheduler_jobs: Running    |
prompt +--------------------------------+
SELECT
    j.job_name,
    j.job_class,
    j.state,
    j.enabled,
    TO_CHAR(j.next_run_date, 'YYYY-MM-DD HH24:MI') AS next_run_date,
    TO_CHAR(j.last_start_date, 'YYYY-MM-DD HH24:MI') AS last_start_date,
    LPAD(EXTRACT(DAY FROM j.last_run_duration), 2, '0') || ' ' ||
    LPAD(EXTRACT(HOUR FROM j.last_run_duration), 2, '0') || ':' ||
    LPAD(EXTRACT(MINUTE FROM j.last_run_duration), 2, '0') AS last_run_duration,
    j.failure_count
FROM
    DBA_SCHEDULER_JOBS j
JOIN
    DBA_SCHEDULER_RUNNING_JOBS r
ON
    j.job_name = r.job_name
ORDER BY
    j.job_name;