Oracle: Short introduction to the Oracle Scheduler

The Oracle-Scheduler is a nice alternative to cronjobs or alike – and it is not as complicated as it may seem at a first glance.

Well, with the package DBMS_SCHEDULER it is possible to define very granular scheduler-objects. There are “programs” that hold the definition for to be executed things, or “schedules” that hold the definition for certain schedule-types. And all of these can be combined to “jobs” consisting of “programs” and “schedules”. And even job-chains are possible, that respond to certain events and start the next job-chain-job dependent on an event.

But if we just want a certain procedure to be executed at a regular intervall it is quite easy. We just need the DBMS_SCHEDULER.CREATE_JOB:

DBMS_SCHEDULER.CREATE_JOB (
   job_name             IN VARCHAR2,
   job_type             IN VARCHAR2,
   job_action           IN VARCHAR2,
   number_of_arguments  IN PLS_INTEGER              DEFAULT 0,
   start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval      IN VARCHAR2                 DEFAULT NULL,
   end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
   enabled              IN BOOLEAN                  DEFAULT FALSE,
   auto_drop            IN BOOLEAN                  DEFAULT TRUE,
   comments             IN VARCHAR2                 DEFAULT NULL,
   credential_name      IN VARCHAR2                 DEFAULT NULL,
   destination_name     IN VARCHAR2                 DEFAULT NULL);

For a simple job that executes my already created procedure “REBUILDINDEXES” every day at 20:00 the following would suffice:

begin
dbms_scheduler.create_job
 (job_name => 'REBUILD_INDEXES',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'REBUILDINDEXES',
  start_date=> trunc(sysdate),
  repeat_interval=> 'FREQ=DAILY; BYHOUR=20;',
  enabled=>true,
  comments=>'Rebuild all indexes dependent on the Index-to-Table-Size-Ratio');
end;

The “job_type” can be one of this:

PLSQL_BLOCK – an anonymous block of PL/SQL-Code
STORED_PROCEDURE – a defined procedure
EXECUTABLE – a shell-script or alike
CHAIN – a job chain

To alter a created job, here to execute the above job every 30 minutes, we use DBMS_SCHEDULER.SET_ATTRIBUTE:

DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          IN {BOOLEAN|DATE|TIMESTAMP|
                        TIMESTAMP WITH TIME ZONE|TIMESTAMP WITH LOCAL TIME ZONE|
                        INTERVAL DAY TO SECOND});
begin
dbms_scheduler.set_attribute
('REBUILD_INDEXES',
'repeat_interval',
'FREQ=MINUTELY; INTERVAL=30');
end;

To see what jobs are created, we use the views USER_SCHEDULER_JOBS or DBA_SCHEDULER_JOBS. There we can see what jobs are enabled, what they shall do or for what time the next run is scheduled.

Information about run jobs can be obtained from the views USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS.

The details of logging and the retention-time of the log-data are controlled by job_classes. Per default our job is set to the “DEFAULT_JOB_CLASS”. Information about all job_classes can be found in DBA_SCHEDULER_JOB_CLASSES.

To create an own JOB_CLASS we use DBMS_SCHEDULER.CREATE_JOB_CLASS.

DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name            IN VARCHAR2,
   resource_consumer_group   IN VARCHAR2 DEFAULT NULL,
   service                   IN VARCHAR2 DEFAULT NULL,
   logging_level             IN PLS_INTEGER
                                DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
   log_history               IN PLS_INTEGER DEFAULT NULL,
   comments                  IN VARCHAR2 DEFAULT NULL);

To alter a JOB_CLASS we use the DBMS_SCHEDULER.SET_ATTRIBUTE like above.

The default log_history is set to 30 days, as we can see in DBA_SCHEDULER_GLOBAL_ATTRIBUTE.

To alter global scheduler attributes we use DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE.

Per default an oracle-maintenance-job is run every nigth at 3 a.m. which purges obsolete log-data according to the JOB_CLASS-Definition. Notice that there are pre-defined job-classes with a log_history of 1 million days; so eventually a regular (manual) check and purge with DBMS_SCHEDULER.PURGE_LOG is needed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: