Task Scheduling in Oracle 10g

I have a working experience in Sql Server 2005 and creating Jobs and scheduling them is just the mater of few clicks in MS SQL Server.

However in Oracle this can be achieved with lot more control

Jobs are creared in Oracle using "DBMS_SCHEDULER" which is internal Oracle package (since Version 10g).I will try to describe about three basic aspects of DBMS_SCHEDULAR which are bare minimum required to create a Job in Oracle.

• Programs
• Schedule
• Jobs

Here Programs and Schedules are individual entities , then Jobs are used to associate a Program with a Schedule.

PROGRAMS

Programs contains the actual task that a scheduler will be required to perform.Oracle supports these tasks implementation in various ways.

DBMS_SCHEDULER.create_program (

program_name => 'plsql_block',

program_type => 'PLSQL_BLOCK',
program_action => 'Any valid pl-sql block.....',
enabled => TRUE,
comments => 'This program will run the above pl-sql block');


Here "program_type" parameter decides how the tasks are written in program. This can be either of these

1. PLSQL_BLOCK
2. EXECUTABLE
3. STORED_PROCEDURE

Here EXECUTABLES can be used to call OS utilities and programs, not just PL/SQL program units. This ability makes it the most comprehensive job management tool for managing Oracle Database and related jobs.

SCHEDULES

Here is the sample sql to create a simple schedular.

DBMS_SCHEDULER.create_schedule (
schedule_name => 'Audit_Purge_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily;byhour=0;byminute=0;bysecond=0',
end_date => NULL,
comments => 'This will run daily');

The most important parameter of Schedular is repeat_interval , some of the mostly used options are
freq
This specifies the type of recurrence. It must be specified. The possible values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
bymonth
This specifies which month or months you want the job to execute in. You can use numbers such as 1 for January and 3 for March, as well as three-letter abbreviations such as FEB for February and JUL for July.
byyearday
This specifies the day of the year as a number. Valid values are 1 to 366. An example is 69, which is March 10 (31 for January, 28 for February, and 10 for March). 69 evaluates to March 10 for non-leap years and March 9 in leap years. -2 will always evaluate to December 30th independent of whether it is a leap year.
bymonthday
This specifies the day of the month as a number. Valid values are 1 to 31. An example is 10, which means the 10th day of the selected month. You can use the minus sign (-) to count backward from the last day, so, for example, BYMONTHDAY=-1 means the last day of the month and BYMONTHDAY=-2 means the next to last day of the month.
byday
This specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on. Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency. Using the minus sign, you can say the second to last Friday of the month. For example, -1 FRI is the last Friday of the month.
byhour
This specifies the hour on which the job is to run. Valid values are 0 to 23. As an example, 10 means 10AM.
byminute
This specifies the minute on which the job is to run. Valid values are 0 to 59. As an example, 45 means 45 minutes past the chosen hour.
bysecond
This specifies the second on which the job is to run. Valid values are 0 to 59. As an example, 30 means 30 seconds past the chosen minute.

JOBS

Jobs are basically the actuall implementation where you combine your task (program) with a time schedule. They can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them:

The first overload will create job entirely of its own, no programs and Schedules

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JobswithNoProgramNoSchedule',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END


The second overload will display a Job defined by an existing program and schedule.

This is the best modular approach to create Job.With this approac you can clearly isolate task with schedule and edit both of them separately.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_sched_job_definition',
program_name => 'test_plsql_block_prog',
schedule_name => 'test_hourly_schedule',
enabled => TRUE,
comments => 'Job defined by an existing program and schedule.');
END


Grant Permissions

If you are planning to use scheduler within a stored procedure, then you must have Create Job privilege to the schema.
Grant Create Job to mySchema

Here myschema is the name schema where schedular is created.
Create Job is the privilege required to execute DBMS_Schedular

Monitoring

Show all schedules
select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;

Show all jobs and their attributes:
select * from dba_scheduler_jobs

Show all program-objects and their attributes
select * from dba_scheduler_programs;

Show all program-arguments:
select * from dba_scheduler_program_args;