DBAtlas · Database operations atlas

Monitor scheduler job run history and failures

OracleScheduler

Command

-- 1) Recent runs: duration, CPU, error code and message
SELECT owner, job_name, status, error#,
       TO_CHAR(actual_start_date,'YYYY-MM-DD HH24:MI:SS') AS started,
       run_duration, cpu_used,
       SUBSTR(additional_info, 1, 200) AS info
FROM   dba_scheduler_job_run_details
WHERE  owner = 'CUSTOMER_APP'
ORDER  BY actual_start_date DESC
FETCH FIRST 20 ROWS ONLY;

-- 2) Failures in the last 24 hours (whole DB):
SELECT owner, job_name, error#, actual_start_date, SUBSTR(errors, 1, 300) AS errors
FROM   dba_scheduler_job_run_details
WHERE  status <> 'SUCCEEDED'
  AND  log_date > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER  BY log_date DESC;

-- 3) Currently running:
SELECT owner, job_name, session_id, running_instance, elapsed_time, cpu_used
FROM   dba_scheduler_running_jobs;

-- 4) Lifecycle events (ENABLE/DISABLE/RUN/STOP...):
SELECT log_date, owner, job_name, operation, status
FROM   dba_scheduler_job_log
WHERE  owner = 'CUSTOMER_APP'
ORDER  BY log_date DESC
FETCH FIRST 20 ROWS ONLY;

-- Log retention (days): EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history', '30');

What it does

Lists, from the scheduler log views, each run's start time, duration, CPU usage, status and error message, plus running jobs and job lifecycle events.

When to use it

When a nightly job did not run or ran long, for job SLA tracking, and when defining failure rules in a monitoring system (OEM/Zabbix/Prometheus).

Caution

Read-only; no license required. Default log retention is 30 days, and jobs with logging_level OFF produce no records. The ERRORS column exists in 12c+; on older releases check ADDITIONAL_INFO. On RAC, RUNNING_INSTANCE shows which node ran the job.

Open in DBAtlas →

Related