User Tools

Site Tools


dba:oracle:docs:dbms_scheduler

[DOC] Oracle Scheduler

Oracle has a built-in scheduler that helps you automate jobs from within the oracle database database. The dbmsscheduler 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 dbascheduler_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 dbaschedulerjobs table for jobs then launches jobs slaves.
  • Jnnn - (job slaves) processes that carry out the task

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

The scheduleradmin role contains all scheduler system privileges, with the adminoption 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 dbmsscheduler.enable%%('%%backupjob');
dbmsscheduler.enable%%('%%backupjob', backupprogram, SYS.windowgroup1);      (enable multiple jobs) | |Disabling|dbmsscheduler.disable('backup_job');

Attributes

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

  • dbmsscheduler.setattribute - <name>,<attribute>,<value>
  • dbmsscheduler.setattribute_null - <name>,<attribute> (set value to NULL)
Alter schedule dbmsscheduler.setattributenull(name⇒ 'testjob', 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 ENDDATE attribute it will be dropped only if the autodrop 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.

  • Jobname - job name * Jobtype - can be any of the following plsqlblock, storedprocedure, executable
  • Jobaction - pl/sql code, stored procedure or a executable * Numberofarguements - the number of arguments that the job accepts range is 0 (default) to 255. * Programname - program associated with this job
  • Startdate - the start date * Repeatinterval - states how often the job should be run (see intervals)
  • Schedulename - identifies the job * Enddate - the end date (job will be set to completed and the enable flag set to false)
  • Jobclass - the class the job is assigned to * Comments - comments * Enabled - true job is enable, false (default) job is disabled * Autodrop - 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 dbmsscheduler.createjob (
  Jobname⇒ 'colajob',
  Jobtype⇒ 'PLSQLBLOCK',
  Jobaction⇒ 'update employees set salary = salary * 1.5;',
  Start
date⇒ '10-oct-2007 06:00:00 am',
  Repeatinterval⇒ 'FREQ=YEARLY',
  Comments⇒ 'Cost of living adjustments'
); | |Display Jobs| select job
name, enabled, runcount from userschedulerjobs; Note: default job is disabled by default (false) | |Copying|dbmsscheduler.copyjob%%('%%colajob', 'raisejob');| |Stopping| dbmsscheduler.stopjob(jobname⇒ 'colajob', force⇒ true); Note: using force stops the job faster | |Deleting| exec dbmsscheduler.dropjob%%('%%colajob'); Note: removes the job permanently
Displaying select jobname, enabled, runcount from userschedulerjobs; Note: copied job is disabled by default(false)
Running dbmsscheduler.runjob('colajob', true);
dbms
scheduler.runjob%%('%%colajob', 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 dbmsscheduler.setattributes(
  name ⇒ 'testjob',
  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 DEFAULTJOBCLASS

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

Logging levels

  • DBMSSCHEDULER.LOGGINGOFF - no logging for any jobs in this class
  • DBMSSCHEDULER.LOGGINGRUNS - info is written to the jobs log (start time, successful, etc)
  • DBMSSCHEDULER.LOGGINGFULL - record management operations on the class, suck as creating new jobs, disable/enabling
Creating dbmsscheduler.createjobclass (
  Job
classname⇒ 'lowpriorityclass',
  Resource
consumergroup⇒ 'lowgroup',
  Logginglevel⇒ DBMSSCHEDULER.LOGGINGFULL,
  Log
history⇒ 60,
  Comment⇒ 'low priority job class'
);
Droppingdbmsscheduler.dropclass('lowpriorityclass, highpriorityclass');
Assigningdbmsscheduler.setattribute(
  name ⇒ 'reportsjobs',
  attribute ⇒ 'job
class',
  value ⇒ 'lowpriorityclass'
);
Prioritizingdbmsscheduler.setattribute(name ⇒ 'reportsjobs', attribute ⇒ 'jobpriority', value ⇒ 2);
Alter attributesdbmsscheduler.alterattributes (
  name ⇒ 'reportsjobs',
  attribute ⇒ 'start
date',
  value ⇒ '15-JAN-08 08:00:00'
);

Scheduler programs

  • Programname - the name of the program * Programtype - can be any of the following plsqlblock, storedprocedure, executable
  • Programaction - pl/sql code, stored procedure or a executable * Numberof_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 dbmsscheduler.createprogram (
  Programname ⇒ 'statsprogram',
  Programtype ⇒ 'storedprocedure',
  Programaction ⇒ 'dbmsstats.gatherschemastats',
  Numberofarguments ⇒ 1,
  Comments ⇒ 'gather stats for a schema'
);
Creating the argument dbmsscheduler.defineprogramargument(
  Program
name ⇒ 'statsprogram',
  Argument
position ⇒ 1,
  Argumenttype ⇒ 'varchar2'
); | |Dropping the argument| dbms
scheduler.dropprogramargument(
  Programname ⇒ 'statsprogram',
  Argumentposition ⇒ 1
); | |Dropping the program| dbms
scheduler.dropprogram(
  Program
name ⇒ 'stats_program',
  force ⇒ true
);

Programs

You use the SETJOBARGUMENTS or SETJOBANYDATA_VALUE to set the program arguments.

  • Programname - programs name * Programtype - can be any of the following plsqlblock, storedprocedure, executable
  • Programaction - pl/sql code, stored procedure or a executable * Numberof_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 dbmsscheduler.createprogram(
  Programname ⇒ 'statsprogram',
  Programtype ⇒ 'storedprocedure',
  Programaction ⇒ 'dbmsstats.gatherschemastats',
  Numberofarguments ⇒ 1,
  Comments ⇒ 'Gather stats for a schema'
);
Define program argumentdbmsscheduler.defineprogramargument(
  program
name ⇒ 'statsprogram',
  argument
position ⇒ 1,
  argumenttype ⇒ 'varchar2'
);| |Drop program argument|dbms
scheduler.dropprogramargument(
  programname ⇒ 'statsprogram',
  argumentposition ⇒ 1
);| |Drop program| dbms
scheduler.dropprogram(
  program
name ⇒ 'statsprogram',
  force ⇒ true
); | |Enable/Disable|dbms
scheduler.enableprogram%%('%%statsprogram');
dbmsscheduler.disableprogram('stats_program');

Schedules

  • Schedulename - name of the schedule must be unique * Startdate - the startdate * Enddate - the enddate * Repeatinterval - states how often the job should be run
  • Comments - comments
Create dbmsscheduler.createschedule(
  schedulename ⇒ 'nightly8schedule',
  start
date ⇒ systimestamp,
  repeatinterval ⇒ 'FREQ=DAILY; BYHOUR=20',
  comments ⇒ 'run nightly at 8:00pm'
); | |Remove|dbms
scheduler.dropschedule%%('%%nightly8_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 dbmsscheduler: <calendarstring>,<startdate>,<returndateafter>,<nextrundate> declare
  start
date timestamp;
  returndateafter timestamp;
  nextrundate timestamp;
BEGIN
  startdate := totimestamptz( '10-oct-2007 10:00:00', 'DD-MON-YYYY HH24:MI:SS')
  return
dateafter := startdate;
  for i in 1..10 loop
     dbmsscheduler.evaluatecalendarstring( 'freq=monthly; interval=2; bymonthday=15', startdate, null,nextrundate);      dbmsoutput.putline('nextrundate: ' || nextrundate);
  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 dbmsruleadm.grantsystemprivilege(dbmsruleadm.createruleobj, 'vallep'),
dbmsruleadm.grantsystemprivilege(dbmsruleadm.createruleobj, 'vallep'),
dbmsruleadm.createevaluationcontextobj, 'vallep')
| |Create|dbms
scheduler.createchain(
  chain
name ⇒ 'testchain',
  rule
setname ⇒ NULL,
  evaluation
interval ⇒ NULL,
  comments ⇒ NULL
);
Define chain dbmsscheduler.definechainstep%%('%%testchain', 'step1', 'program1');
dbmsscheduler.definechainstep%%('%%testchain', 'step2', 'program2');
dbmsscheduler.definechainstep%%('%%testchain', 'step3', 'program3'); Note: a chain step can point to a program, an event or another chain
Define chain rules dbmsscheduler.definechainrule%%('%%testchain', 'TRUE', 'START step1');
dbmsscheduler.definechainrule%%('%%testchain', 'step1 completed', 'start step2, step3');
dbmsscheduler.definechainrule%%('%%testchain', '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
dbmsscheduler.createjob(
  jobname ⇒ 'testchainjob',
  job
type ⇒ 'CHAIN',
  jobaction ⇒ 'testchain',
  repeatinterval ⇒ 'freq=daily;byhour=13;byminute=0;bysecond=0',
  enabled ⇒ true
);
END; OR BEGIN
dbms
scheduler.runchain(
  chain
name ⇒ 'mychain1',
  job
name ⇒ 'quickchainjob',
  startsteps ⇒ 'mystep1, mystep2');
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

eventcondition|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.userdata. Review the dbmsaqadm package to learn about advanced queuing and related rules.| |queuespecdetermines 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
dbmsscheduler.createjob(
  jobname ⇒ 'testjob',
  programname ⇒ 'testprogram',
  startdate ⇒ '15-JAN-08 08:00:00',
  event
condition ⇒ 'tab.userdata.eventname = ''FILEARRIVAL%%',\\   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 = %%FILEARRIVAL'
     and extract hour from tab.userdata.eventtimestamp < 12',
  queuespec ⇒ 'testevents_q');
END; Note: the schedule will start the job when the event indicates that a file has arrived before noon

Windows

  • Windowname - name of window in the SYS schema * Resourceplan - the resource plan used by the window
  • Startdate - the start date * Duration - how long the window stays open * Schedulename - schedule name associated with window
  • Repeatinterval - how often the window repeats * Enddate - the enddate * Windowpriority - only relevant when 2 windows are open, values are LOW (default) or HIGH
Creating a window using a schedule (so schedule will open window) dbmsscheduler.createwindow(
  windowname ⇒ 'workhourswindow',
  start
date ⇒ '14-JAN-08 08:00:00',
  duration ⇒ interval '10' hour,
  resourceplan ⇒ 'dayplan',
  schedulename ⇒ 'workhoursschedule',
  window
priority ⇒ 'high',
  comment ⇒ 'Work Hours Window'
);
Opening a window manually dbmsscheduler.openwindow(
  windowname ⇒ 'workhourswindow',
  duration ⇒ interval '20' minute,
  force ⇒ true
); | | Closing window manually |dbms
scheduler.closewindow( windowname⇒ 'workhourswindow' );
Disable windowdbmsscheduler.disablewindow( name ⇒ 'workhourswindow');
Displaying window logs select logid, trunc(logdate) logdate, windowname, operation from dbaschedulerwindowlog;
select log
id, trunk(logdate) windowname, actualduration from dbaschedulerwindowdetails;

Purging logs

Purge Logs dbmsschedule.purgelog(loghistory ⇒ 14, whichlog ⇒ 'JOBLOG'); | |Set scheduler log parameter|dbmsscheduler.setschedulerattribute( 'loghistory', '60');
dbms
scheduler.setschedulerattribute( whichlog⇒ [windowlog | 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: <code>select jobname, status, error# from dbaschedulerjobrundetails where jobname = 'FAILJOB';</code> * Run count per job: <code>select jobname, state, runcount from dbaschedulerjobs;</code> * Next events: <code>select windowname, nextstartdate from dbaschedulerwindows;</code>
  • Query the log: <code>select logid, trunc(logdate) logdate, owner, jobname, operation from dbaschedulerjoblog order by logid;</code>
  • Longer info about a job: <code> col LOGID FORMAT 9999; COL OWNER FORMAT A10; COL LOGDATE FORMAT A40 ; COL JOBNAME FORMAT A30; COL JOBSUBNAME FORMAT A10 WORD WRAPPED ; COL JOBCLASS FORMAT A25 WORD WRAPPED ; COL OPERATION FORMAT A10 ; COL STATUS FORMAT A10 ; select LOGID, OWNER, LOGDATE, JOBNAME, JOBCLASS, OPERATION, STATUS, ADDITIONALINFO from dbaschedulerjoblog where LOGID=JOBID ; </code> * Even more info about the job: <code> col LOGID FORMAT 9999; COL OWNER FORMAT A10; COL LOGDATE FORMAT A40 ; COL JOBNAME FORMAT A30; COL JOBSUBNAME FORMAT A10 WORD WRAPPED ; COL JOBCLASS FORMAT A25 WORD WRAPPED ; COL OPERATION FORMAT A10 ; COL STATUS FORMAT A10 ; select LOGID, OWNER, LOGDATE, JOBNAME, STATUS, ADDITIONALINFO from DBASCHEDULERJOBRUNDETAILS where LOGID=JOBID ; </code>
Useful Views Description
%%%%schedulerschedules|all defined schedules| ^%%%%schedulerprogramsall defined programs
dba/oracle/docs/dbms_scheduler.txt · Last modified: 2023/01/31 08:23 by dodger