Oracle Scheduler

Oracle has a built-in scheduler that helps you automate jobs from within the oracle database database. The dbms_scheduler package contains various functions and procedures that manage the scheduler, although this can also be achieved via the OEM. The scheduler is like cron, it will schedule jobs at particular time and run them. All scheduler tasks can be views through dba_scheduler_jobs view. You cannot schedule Operating System jobs (either scripts or Binary) via the scheduler this must be done via cron

The scheduler uses a modular approach to managing tasks which enables the reuse of similar jobs.

Basic scheduler components

The scheduler has 5 basic components

Jobs a job instructs the scheduler to run a specific program at a specific date/time, a job can run execute PL/SQL code, a native binary executable, java application or a shell scripts.

when and how frequently a job should run (start date, optional end date, repeat interval), you can also run a job when a specific database event occurs.

Programs contains the metadata about a scheduler job. A program includes the program name, the program type (PL/SQL, shell script) and the program action which is the actual name of the program or script to run.
Events the scheduler uses oracle streams advanced queuing feature to raise events and start database jobs based on the events. An event is a message sent by an application or process when it notices some action or occurrence.
Chains you can use the concept of a scheduler chain to link related programs together. Thus running of a specific program could be made contingent on the successful running of certain other programs.

Advanced scheduler components

Job Classes (groups)

associate one or more jobs with a resource manager consumer group and also control logging levels, you can use classes to perform

  • assign job priority levels for individual jobs, with higher-priority jobs always starting before a lower-priority job
  • specify common attributes for a set of jobs

a window in date/time when a job should launch a interval of time when the job can run

Window Groups logical method of grouping windows

Scheduler Architecture

The architecture consists of the job table, job coordinator and the job workers (slaves), the job table contains information about jobs (job name, program name and job owner). The job coordinator regularly looks in the job table to find out what jobs to execute, the job coordinator creates and manages the job worker processes which actually execute the job.


Note: Jnnn is limited by the JOB_QUEUE_PROCESSES, default = 10, if zero scheduler will not run (only requirement to start Scheduler)

The scheduler_admin role contains all scheduler system privileges, with the admin_option clause, it will allow you to

There are a number of privileges regarding the scheduler


When enabling a job all sub-jobs are enabled, when enabling a window only that window gets enabled not sub-windows, when referencing a window always prefix with a SYS.


dbms_scheduler.enable('backup_job', backup_program, SYS.window_group_1);      (enable multiple jobs)

Disabling dbms_scheduler.disable('backup_job');


 These are the only way to alter a schedule. By default objects are set to false when created.

Alter schedule

dbms_scheduler.set_attribute_null(name=> 'test_job', attribute=> 'end_date');

Note: sets end date to NULL

Creating a job

A schedule defined within a job object is know as an inline schedule, where as an independent schedule object is referred to as a stored schedule. Inline schedules cannot be reference by any other objects.

When a job exceeds its END_DATE attribute it will be dropped only if the auto_drop attribute is set to true, otherwise it will be disabled. In either case the state column will be set to completed in the job table.

Jobs support an overload procedure based on the number of arguments.

Create Job

dbms_scheduler.create_job (
  Job_name=> 'cola_job',
  Job_type=> 'PLSQL_BLOCK',
  Job_action=> 'update employees set salary = salary * 1.5;',
  Start_date=> '10-oct-2007 06:00:00 am',
  Repeat_interval=> 'FREQ=YEARLY',
  Comments=> 'Cost of living adjustments'

Display Jobs

select job_name, enabled, run_count from user_scheduler_jobs;

Note: default job is disabled by default (false)

Copying dbms_scheduler.copy_job('cola_job', 'raise_job');

dbms_scheduler.stop_job(job_name=> 'cola_job', force=> true);

Note: using force stops the job faster


exec dbms_scheduler.drop_job('cola_job');

Note: removes the job permanently


select job_name, enabled, run_count from user_scheduler_jobs;

Note: copied job is disabled by default(false)


dbms_scheduler.run_job('cola_job', true);
dbms_scheduler.run_job('cola_job', false);

true - runs immediately, synchronously, control does not return to user, no run count update
false - runs immediately, asynchronously, control does return to user, updates run count


  name => 'test_job',
  attribute => 'job_priority',
  value => 1

Note: priorities are between 1-5, 1 being the highest (default is 3)

Job Classes

All jobs must belong to one class default is DEFAULT_JOB_CLASS

Logging levels


dbms_scheduler.create_job_class (
  Job_class_name=> 'low_priority_class',
  Resource_consumer_group=> 'low_group',
  Log_history=> 60,
  Comment=> 'low priority job class'

Dropping dbms_scheduler.drop_class('low_priority_class, high_priority_class');
Assigning dbms_scheduler.set_attribute(
  name => 'reports_jobs',
  attribute => 'job_class',
  value => 'low_priority_class'
Prioritizing dbms_scheduler.set_attribute(name => 'reports_jobs', attribute => 'job_priority', value => 2);
Alter attributes dbms_scheduler.alter_attributes (
  name => 'reports_jobs',
  attribute => 'start_date',
  value => '15-JAN-08 08:00:00'

Scheduler programs

Creating the program

dbms_scheduler.create_program (
  Program_name => 'stats_program',
  Program_type => 'stored_procedure',
  Program_action => 'dbms_stats.gather_schema_stats',
  Number_of_arguments => 1,
  Comments => 'gather stats for a schema'

Creating the argument

  Program_name => 'stats_program',
  Argument_position => 1,
  Argument_type => 'varchar2'

Dropping the argument

  Program_name => 'stats_program',
  Argument_position => 1

Dropping the program

  Program_name => 'stats_program',
  force => true


You use the SET_JOB_ARGUMENTS or SET_JOB_ANYDATA_VALUE to set the program arguments.

Creating programs

  Program_name => 'stats_program',
  Program_type => 'stored_procedure',
  Program_action => 'dbms_stats.gather_schema_stats',
  Number_of_arguments => 1,
  Comments => 'Gather stats for a schema'

Define program argument dbms_scheduler.define_program_argument(
  program_name => 'stats_program',
  argument_position => 1,
  argument_type => 'varchar2'
Drop program argument dbms_scheduler.drop_program_argument(
  program_name => 'stats_program',
  argument_position => 1
Drop program

  program_name => 'stats_program',
  force => true

Enable/Disable dbms_scheduler.enable_program('stats_program');



  schedule_name => 'nightly_8_schedule',
  start_date => systimestamp,
  repeat_interval => 'FREQ=DAILY; BYHOUR=20',
  comments => 'run nightly at 8:00pm'

Remove dbms_scheduler.drop_schedule('nightly_8_schedule');


Interval elements

Interval rules

Interval examples

Testing Interval

dbms_scheduler: <calendar_string>,<start_date>,<return_date_after>,<next_run_date>

  start_date timestamp;
  return_date_after timestamp;
  next_run_date timestamp;
  start_date := to_timestamp_tz( '10-oct-2007 10:00:00', 'DD-MON-YYYY HH24:MI:SS')
  return_date_after := start_date;
  for i in 1..10 loop
     dbms_scheduler.evaluate_calendar_string( 'freq=monthly; interval=2; bymonthday=15', start_date, null,next_run_date);      dbms_output.put_line('next_run_date: ' || next_run_date);
  end loop;

Managing Chains

In order to manage chains you need both the create job and rules engine privileges, their are many other options that allow you to drop a chain, drop rules from a chain, disable a chain, alter a chain and so on (see the Oracle docs for more information)


dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_rule_obj, 'vallep'),
dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_rule_obj, 'vallep'),
dbms_rule_adm.create_evaluation_context_obj, 'vallep')

Create dbms_scheduler.create_chain(
  chain_name => 'test_chain',
  rule_set_name => NULL,
  evaluation_interval => NULL,
  comments => NULL
Define chain

dbms_scheduler.define_chain_step('test_chain', 'step1', 'program1');
dbms_scheduler.define_chain_step('test_chain', 'step2', 'program2');
dbms_scheduler.define_chain_step('test_chain', 'step3', 'program3');

Note: a chain step can point to a program, an event or another chain

Define chain rules

dbms_scheduler.define_chain_rule('test_chain', 'TRUE', 'START step1');
dbms_scheduler.define_chain_rule('test_chain', 'step1 completed', 'start step2, step3');
dbms_scheduler.define_chain_rule('test_chain', 'step2 completed and step3 completed', end);

the 1st rule states that step1 should be run, which means the scheduler will start program1
the 2nd rule states that step2 and step3 should run if step1 has completed sucessfully
the final rule states that when step2 and step3 finish the chain will end

Embedding Jobs in Chains

  job_name => 'test_chain_job',
  job_type => 'CHAIN',
  job_action => 'test_chain',
  repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',
  enabled => true


  chain_name => 'my_chain1',
  job_name => 'quick_chain_job',
  start_steps => 'my_step1, my_step2');

Note: the first option creates a job which runs the chain, you also have the option of using run_chain to run a chain without creating a job first.

Managing Events

You can create both jobs and schedules that are based strictly on events and not calendar time. There are two attributes that need highlighting

event_condition is conditional expression that takes its value from the event source queue table and uses Oracle streams advanced queuing rules. You specify object attributes in this expression and prefix them with tab.user_data. Review the dbms_aqadm package to learn about advanced queuing and related rules.
queue_spec determines the queue into which the job-triggering event will be queued.

There are many more options than below please refer to the Oracle documentation for a full listing.

Create event based Job

  job_name => 'test_job',
  program_name => 'test_program',
  start_date => '15-JAN-08 08:00:00',
  event_condition => 'tab.user_data.event_name = ''FILE_ARRIVAL''',
  queue_spec => 'test_events_q',
  enabled => true,
  comments => 'An event based job');

Note: the job will run when the event indicates that a file has arrived.

Create event based schedule

  schedule_name => 'appowner.file_arrival',
  start_date => systimestamp,
  event_condition => 'tab.user_data.object_owner = ''APPOWNER''
     and tab.user_data.event_name = ''FILE_ARRIVAL'
     and extract hour from tab.user_data.event_timestamp < 12',
  queue_spec => 'test_events_q');

Note: the schedule will start the job when the event indicates that a file has arrived before noon


Creating a window using a schedule (so schedule will open window)

  window_name => 'work_hours_window',
  start_date => '14-JAN-08 08:00:00',
  duration => interval '10' hour,
  resource_plan => 'day_plan',
  schedule_name => 'work_hours_schedule',
  window_priority => 'high',
  comment => 'Work Hours Window'

Opening a window manually

  window_name => 'work_hours_window',
  duration => interval '20' minute,
  force => true

Closing window manually

dbms_scheduler.close_window( window_name=> 'work_hours_window' );
Disable window dbms_scheduler.disable_window( name => 'work_hours_window');
Displaying window logs

select log_id, trunc(log_date) log_date, window_name, operation from dba_scheduler_window_log;
select log_id, trunk(log_date) window_name, actual_duration from dba_scheduler_window_details;

Purging logs

Purge Logs

dbms_schedule.purge_log(log_history => 14, which_log => 'JOB_LOG');

Set scheduler log parameter dbms_scheduler.set_scheduler_attribute( 'log_history', '60');
dbms_scheduler.set_scheduler_attribute( which_log=> [window_log | job_log], '60');

Display information


select job_name, status, error# from dba_scheduler_job_run_details where job_name = 'FAIL_JOB';
select job_name, state, run_count from dba_scheduler_jobs;
select job_name, state, run_count from user_scheduler_jobs;
select window_name, next_start_date from dba_scheduler_windows;
select log_id, trunc(log_date) log_date, owner, job_name, operation from dba_scheduler_job_log order by log_id;

Useful Views
*_scheduler_schedules all defined schedules
*_scheduler_programs all defined programs
*_scheduler_program_arguments all registered program arguments and default values if exist
*_scheduler_jobs all defined jobs both enabled and disabled and if they are running/executing
*_scheduler_global_attribute current values of all scheduler attributes
*_scheduler_job_classes all defined job classes
*_scheduler_windows all defined windows
*_scheduler_job_run_details details on all completed (successful or failed) jobs
*_scheduler_window_groups all window groups
*_scheduler_wingroup_members all members of all groups
*_scheduler_running_jobs the state info on all jobs that are currently being run/executed.
*_scheduler_job_run_details check status and duration of execution for all jobs
*_scheduler_job_log enables you to audit job-management activities

Default Jobs