List scheduled jobs in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-10

Table of Contents:


    Article for: Oracle database 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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept