List scheduled jobs in Oracle database

Article for: MySQL MariaDB

Query below return all events scheduled in Oracle database with details.

Query

select owner as schema_name,
       job_name,
       job_style,
       case when job_type is null 
                 then 'PROGRAM'
            else job_type end as job_type,  
       case when job_type is null
                 then program_name
                 else job_action end as job_action,
       start_date,
       case when repeat_interval is null
            then schedule_name
            else repeat_interval end as schedule,
       last_start_date,
       next_run_date,
       state
from sys.all_scheduler_jobs
order by owner,
         job_name;

Columns

  • schema_name - name of the schema
  • job_name - name of the job
  • job_style:
    • REGULAR
    • LIGHTWEIGHT
  • job_type - inline job action type
    • PLSQL_BLOCK
    • STORED_PROCEDURE
    • EXECUTABLE
    • CHAIN
    • SQL_SCRIPT
    • BACKUP_SCRIPT
    • EXTERNAL_SCRIPT
    • PROGRAM
  • job_action - PL/SQL code or program/routine name to call
  • start_date - when job will be launched first time
  • schedule - inline schedule PL/SQL expression, calendar string or name of the schedule
  • last_start_date - last date on which the job started running
  • next_run_date: next date on which the job is scheduled to run
  • state:
    • Disabled
    • Scheduled

Rows

  • One row: represents one job
  • Scope of rows: all jobs in database
  • Ordered by job schema, job name

Sample results

0
There are no comments. Click here to write the first comment.