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.
Schedules

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
Windows

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.

Processes

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

Enabling/Disabling

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.

Enabling

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

Disabling dbms_scheduler.disable('backup_job');

Attributes

 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');
Stopping

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

Note: using force stops the job faster
Deleting

exec dbms_scheduler.drop_job('cola_job');

Note: removes the job permanently
Displaying

select job_name, enabled, run_count from user_scheduler_jobs;

Note: copied job is disabled by default(false)
Running

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

Note:
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
Priority

dbms_scheduler.set_attributes(
  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

Creating

dbms_scheduler.create_job_class (
  Job_class_name=> 'low_priority_class',
  Resource_consumer_group=> 'low_group',
  Logging_level=> DBMS_SCHEDULER.LOGGING_FULL,
  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

dbms_scheduler.define_program_argument(
  Program_name => 'stats_program',
  Argument_position => 1,
  Argument_type => 'varchar2'
);

Dropping the argument

dbms_scheduler.drop_program_argument(
  Program_name => 'stats_program',
  Argument_position => 1
);

Dropping the program

dbms_scheduler.drop_program(
  Program_name => 'stats_program',
  force => true
);

Programs

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

Creating programs

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'
);
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

dbms_scheduler.drop_program(
  program_name => 'stats_program',
  force => true
);
Enable/Disable dbms_scheduler.enable_program('stats_program');
dbms_scheduler.disable_program('stats_program');

Schedules

Create

dbms_scheduler.create_schedule(
  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');

Intervals

Interval elements

Interval rules

Interval examples

Testing Interval

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

declare
  start_date timestamp;
  return_date_after timestamp;
  next_run_date timestamp;
BEGIN
  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;
END;
/

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)

Privilege

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);

Note:
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

BEGIN
dbms_scheduler.create_job(
  job_name => 'test_chain_job',
  job_type => 'CHAIN',
  job_action => 'test_chain',
  repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',
  enabled => true
);
END;

OR

BEGIN
dbms_scheduler.run_chain(
  chain_name => 'my_chain1',
  job_name => 'quick_chain_job',
  start_steps => 'my_step1, my_step2');
END;

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

BEGIN
dbms_scheduler.create_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');
END;

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

BEGIN
dbms_scheduler.create_event_scheule(
  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');
END;

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

Windows

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

dbms_scheduler.create_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

dbms_scheduler.open_window(
  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

Display

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