User Tools

Site Tools


dbms_scheduler

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

Jobsa 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.
Programscontains 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.
Eventsthe 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.
Chainsyou 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 Groupslogical 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.

  • Job table - houses all the active jobs
  • Job coordinator - insures that jobs are being run on time, spawns and removes slave jobs, write/read job info to/from cache, query job table, pass job information to jobs slaves
  • Job slaves - process that carry out the jobs. Updates job log when job completes.

Processes

  • CJQ0 - (job coordinator) monitors dba_scheduler_jobs table for jobs then launches jobs slaves.
  • Jnnn - (job slaves) processes that carry out the task

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

  • create, drop or alter job classes, windows and window groups
  • stop any job
  • start and stop windows prematurely

There are a number of privileges regarding the scheduler

  • Create job
  • Create any job
  • Execute any program
  • Execute any class
  • Manage scheduler
  • Execute on <job, program or class>
  • Alter on <job, program or class>
  • All on <job, program or class>

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)
Disablingdbms_scheduler.disable('backup_job');

Attributes

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

  • dbms_scheduler.set_attribute - <name>,<attribute>,<value>
  • dbms_scheduler.set_attribute_null - <name>,<attribute> (set value to NULL)
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.

  • Job_name - job name
  • Job_type - can be any of the following plsql_block, stored_procedure, executable
  • Job_action - pl/sql code, stored procedure or a executable
  • Number_of_arguements - the number of arguments that the job accepts range is 0 (default) to 255.
  • Program_name - program associated with this job
  • Start_date - the start date
  • Repeat_interval - states how often the job should be run (see intervals)
  • Schedule_name - identifies the job
  • End_date - the end date (job will be set to completed and the enable flag set to false)
  • Job_class - the class the job is assigned to
  • Comments - comments
  • Enabled - true job is enable, false (default) job is disabled
  • Auto_drop - the jobs is dropped once completed (run once only jobs) default is true.

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

  • Group larger jobs together, characteristics can be inherited by all jobs within the group
  • Classes can be assigned to a resource consumer group
  • Jobs can prioritize with the class

All jobs must belong to one class default is DEFAULT_JOB_CLASS

  • Job_class_name - unique name within the sys schema
  • Resource_consumer_group - resource group to which job belongs
  • Service - service which the jobs belongs to, used in RAC
  • Logging_level - (see below)
  • Log_history - how long log history is kept default is 30 days
  • Comments - comments

Logging levels

  • DBMS_SCHEDULER.LOGGING_OFF - no logging for any jobs in this class
  • DBMS_SCHEDULER.LOGGING_RUNS - info is written to the jobs log (start time, successful, etc)
  • DBMS_SCHEDULER.LOGGING_FULL - record management operations on the class, suck as creating new jobs, disable/enabling
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'
);
Droppingdbms_scheduler.drop_class('low_priority_class, high_priority_class');
Assigningdbms_scheduler.set_attribute(
  name ⇒ 'reports_jobs',
  attribute ⇒ 'job_class',
  value ⇒ 'low_priority_class'
);
Prioritizingdbms_scheduler.set_attribute(name ⇒ 'reports_jobs', attribute ⇒ 'job_priority', value ⇒ 2);
Alter attributesdbms_scheduler.alter_attributes (
  name ⇒ 'reports_jobs',
  attribute ⇒ 'start_date',
  value ⇒ '15-JAN-08 08:00:00'
);

Scheduler programs

  • Program_name - the name of the program
  • Program_type - can be any of the following plsql_block, stored_procedure, executable
  • Program_action - pl/sql code, stored procedure or a executable
  • Number_of_arguements - the number of arguments that the job accepts range is 0 (default) to 255.
  • Enabled - true job is enable, false (default) job is disabled
  • Comments - comments
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.

  • Program_name - programs name
  • Program_type - can be any of the following plsql_block, stored_procedure, executable
  • Program_action - pl/sql code, stored procedure or a executable
  • Number_of_arguments - the number of arguments that the job accepts range is 0 (default) to 255.
  • Enabled - true job is enable, false (default) job is disabled
  • Comments - comments
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 argumentdbms_scheduler.define_program_argument(
  program_name ⇒ 'stats_program',
  argument_position ⇒ 1,
  argument_type ⇒ 'varchar2'
);
Drop program argumentdbms_scheduler.drop_program_argument(
  program_name ⇒ 'stats_program',
  argument_position ⇒ 1
);
Drop program dbms_scheduler.drop_program(
  program_name ⇒ 'stats_program',
  force ⇒ true
);
Enable/Disabledbms_scheduler.enable_program('stats_program');
dbms_scheduler.disable_program('stats_program');

Schedules

  • Schedule_name - name of the schedule must be unique
  • Start_date - the start_date
  • End_date - the end_date
  • Repeat_interval - states how often the job should be run
  • Comments - comments
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'
);
Removedbms_scheduler.drop_schedule('nightly_8_schedule');

Intervals

Interval elements

  • FREQ - required and values are yearly, monthly, weekly, daily, hourly, minutely, secondly
  • INTERVAL - how often it repeats default 1 means every day, 2 would be every other day
  • BYMONTHLY - can use (1-12) or (JAN-DEC) or (1,3,12), etc
  • BYWEEKNO - the week number
  • BYYEARDAY - the date of the year as a number
  • BYMONTHDAY - (1-31), -1 eans last day of the month
  • BYDAY - (MON-SUN)
  • BYHOUR - (0-23)
  • BYMINUTE - (0-59)
  • BYSECOND - (0-59)

Interval rules

  • Frequency must be first element
  • Elements must be separated by a semi-colon and each one can only be represented once.
  • Element values must be separated by a comma.
  • Elements are case-insensitive and whitespaces are allowed
  • BYWEEKNO, frequency must be set to yearly
  • Can use negative numbers (-1) with the BY elements ( BYMONTH -1 will return last day of month)
  • BYDAY when used with yearly or monthly you can use -1SAT last Saturday of month -2SAT second last Saturday, etc
  • Monday is always the first day of the week.
  • Calendaring does not use time zones/ daylight saving (dst)

Interval examples

  • Every Monday - freq=weekly; byday=mon
  • Every other Monday - freq=weekly; byday=mon interval=2;
  • Last day of each month - freq=monthly; bymonthday=-1;
  • Every 7 jan - freq=yearly; bymonth=jan; bymonthday=7;
  • 2 nd wed of each month - freq=monthly; byday=2wed;
  • Every hour - freq=hourly
  • Every 4 hours - freq=hourly; interval=4;
  • Hourly on 1 st day of month - freq=hourly; bymonthday=1;
  • 15 th day of every other month - freq=monthly; bymonthday=15; interval=2
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')
Createdbms_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_conditionis 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_specdetermines 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

  • Window_name - name of window in the SYS schema
  • Resource_plan - the resource plan used by the window
  • Start_date - the start date
  • Duration - how long the window stays open
  • Schedule_name - schedule name associated with window
  • Repeat_interval - how often the window repeats
  • End_date - the end_date
  • Window_priority - only relevant when 2 windows are open, values are LOW (default) or HIGH
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 windowdbms_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 parameterdbms_scheduler.set_scheduler_attribute( 'log_history', '60');
dbms_scheduler.set_scheduler_attribute( which_log⇒ [window_log | job_log], '60');

Display information/Querys

  • Stopping the scheduler:
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE') ; 
  • Starting the scheduler:
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE') ;
  • Checking the status:
select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;
  • Info about a finished job:
select job_name, status, error# from dba_scheduler_job_run_details where job_name = 'FAIL_JOB';
  • Run count per job:
select job_name, state, run_count from dba_scheduler_jobs;
  • Next events:
select window_name, next_start_date from dba_scheduler_windows;
  • Query the log:
select log_id, trunc(log_date) log_date, owner, job_name, operation from dba_scheduler_job_log order by log_id;
  • Longer info about a job:
col LOG_ID FORMAT 9999;
COL OWNER FORMAT A10;
COL LOG_DATE FORMAT A40 ;
COL JOB_NAME FORMAT A30;
COL JOB_SUBNAME FORMAT A10 WORD WRAPPED ;
COL JOB_CLASS FORMAT A25 WORD WRAPPED ;
COL OPERATION FORMAT A10 ;
COL STATUS FORMAT A10 ;
select LOG_ID, OWNER, LOG_DATE, JOB_NAME, JOB_CLASS, OPERATION, STATUS, ADDITIONAL_INFO
	from dba_scheduler_job_log 
	where LOG_ID=JOB_ID ;

* Even more info about the job:

col LOG_ID FORMAT 9999;
COL OWNER FORMAT A10;
COL LOG_DATE FORMAT A40 ;
COL JOB_NAME FORMAT A30;
COL JOB_SUBNAME FORMAT A10 WORD WRAPPED ;
COL JOB_CLASS FORMAT A25 WORD WRAPPED ;
COL OPERATION FORMAT A10 ;
COL STATUS FORMAT A10 ;
select LOG_ID, OWNER, LOG_DATE, JOB_NAME,  STATUS, ADDITIONAL_INFO
	from DBA_SCHEDULER_JOB_RUN_DETAILS
	where LOG_ID=JOB_ID ;
Useful Views Description
*_scheduler_schedulesall defined schedules
*_scheduler_programsall defined programs
*_scheduler_program_argumentsall registered program arguments and default values if exist
*_scheduler_jobsall defined jobs both enabled and disabled and if they are running/executing
*_scheduler_global_attributecurrent values of all scheduler attributes
*_scheduler_job_classesall defined job classes
*_scheduler_windowsall defined windows
*_scheduler_job_run_detailsdetails on all completed (successful or failed) jobs
*_scheduler_window_groupsall window groups
*_scheduler_wingroup_membersall members of all groups
*_scheduler_running_jobsthe state info on all jobs that are currently being run/executed.
*_scheduler_job_run_detailscheck status and duration of execution for all jobs
*_scheduler_job_logenables you to audit job-management activities

Default Jobs

  • PURGE_LOG - deletes entries from the scheduler job if more than 30 days
  • GATHER_STATS_JOBS - gathers optimiser statistics has two windows weeknight_window and weekend_window.

Links

dbms_scheduler.txt · Last modified: 2013/03/26 11:30 by dodger