Oracle Scheduler SQL Cheat Sheet

 Oracle Scheduler is a built-in job scheduler in Oracle Database that enables you to manage and execute various tasks, such as running database program units, external executables, and scripts. It provides a flexible and sophisticated way to schedule jobs based on time, events, or dependencies, allowing you to automate routine tasks and reduce manual intervention.

The Scheduler offers advanced features, including prioritization of jobs based on business requirements, resource allocation, and monitoring of job execution. It also supports execution of jobs in a clustered environment, such as Oracle Real Application Clusters (Oracle RAC). With Oracle Scheduler, you can streamline your database operations, improve reliability, and reduce operating costs.

You can use following SQLs to manage your Scheduler and the jobs:

Check Scheduler Job Details in CDB

SQL

-- View job details in the Container Database (CDB)

SELECT

  CON_ID,

  JOB_NAME,

  JOB_TYPE,

  ENABLED,

  STATE,

  NEXT_RUN_DATE,

  REPEAT_INTERVAL

FROM

  cdb_scheduler_jobs;

Monitor Currently Running Jobs

SQL

-- View currently running jobs

SELECT

  job_name,

  session_id,

  running_instance,

  elapsed_time

FROM

  dba_scheduler_running_jobs;

View Job Run Details

SQL

-- View details of job runs

SELECT

  *

FROM

  DBA_SCHEDULER_JOB_RUN_DETAILS;

View Job-Related Logs

SQL

-- View logs related to job execution

SELECT

  *

FROM

  DBA_SCHEDULER_JOB_LOG;

Check All Scheduler Schedules

SQL

-- Set formatting options for output

SET PAGESIZE 200

SET LINES 299

COL START_DATE FOR A45

COL REPEAT_INTERVAL FOR A45

COL schedule_name FOR A34

-- View all scheduler schedules

SELECT

  schedule_name,

  schedule_type,

  start_date,

  repeat_interval

FROM

  dba_scheduler_schedules;

History of All Scheduler Job Runs

SQL

-- Set formatting options for output

SET PAGESIZE 299

SET LINES 299

COL JOB_NAME FOR A24

COL actual_start_date FOR A56

COL RUN_DURATION FOR A34

-- View history of all scheduler job runs

SELECT

  job_name,

  status,

  actual_start_date,

  run_duration

FROM

  DBA_SCHEDULER_JOB_RUN_DETAILS

ORDER BY

  ACTUAL_START_DATE DESC;

Check Log Information for All Scheduler Jobs

SQL

-- Set formatting options for output

SET PAGESIZE 299

SET LINES 299

COL job_name FOR A24

COL log_date FOR A40

COL operation FOR A19

COL additional_info A79

-- View log information for all scheduler jobs

SELECT

  job_name,

  log_date,

  status,

  OPERATION,

  ADDITIONAL_INFO

FROM

  dba_scheduler_job_log

ORDER BY

  log_date DESC;

Check All Scheduler Windows Details

SQL

-- Set formatting options for output

SET PAGESIZE 300

SET LINESIZE 200

-- View all scheduler windows details

SELECT

  *

FROM

  dba_scheduler_windows;