1.0 Introduction

As a database administrator there are many times when we have to run something at regular intervals. Oracle did provide us with this ability of scheduling jobs via DBMS_JOB from Oracle 8i onwards (which is still present in 10g, so that old code does not break ), however Oracle 10g introduces a new scheduling system which is more powerful, robust and flexible than dbms_job, but it is slightly complicated to get straight in your head. From the first impression one can say that oracle could not have done a better job of obfuscating a simple thing like running jobs at regular interval. But if you take a closer look you would appreciate the fact that the complexity arises due to the power and flexibility of this scheduling system.

This new scheduling system is the way for scheduling jobs in future, though dbms_job still works in Oracle 10g you should start using Oracle 10g's scheduling system.

Oracle provides two interfaces to access this new scheduler, for all you command line enthusiast you would love the command line interface dbms_scheduler (built in plsql package), if you like doing stuff using GUI interface you would need to get familiar with Oracle Enterprise Manager Database Control. I will only cover using command line apis here.

2.0 Preparations

First let me setup the user, I will use to demonstrate the use of dbms scheduler. I will setup this user with minimum privileges needed to schedule jobs.

Here I have created a user poddar. I have granted create job system privilege to poddar, this privilege allows poddar to create job in his own schema. If I wanted poddar to create jobs in other schemas, I could have granted poddar create any job system privilege. But for this article, create job privilege would be sufficient. I will also need to create a procedure as poddar which I would schedule using dbms_scheduler. Therefore I will grant create procedure system privilege, and create table system privilege to poddar, though these privileges have nothing to do with scheduling jobs perse.

Now I will connect as poddar and create a table Employee and a procedure insert_employee, which just inserts a row into the Employee table. I will then schedule this procedure to run every two minutes using dbms_scheduler. The table employee has two columns, employee number and hire date. insert_employee will always insert same number as employee number but it will insert current timestamp as hire date.

3.0 Scheduling

Now lets schedule out insert_employee procedure to run every 2 minutes. Scheduling job in Oracle 10g requires us to provide following information:

Job Name: This could be any valid oracle identifier but it cannot be the same as any existing object in the database.

Job Type: There are three job types possible PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE. You will use PLSQL_BLOCK when you want to type a anonymous plsql block directly into dbms_scheduler call, use STORED_PROCEDURE when you want to schedule a existing stored procedure, pacage procdure or a function. will use EXECUTABLE when you want the scheduler to run a operating system program or script.

Job Action: Job action is, what you want to schedule. It is an anonymous plsql block if job_type is PLSQL_BLOCK, is a stored procedure, package procedure or function if job_type is STORED_PROCEDURE, is the full name (including full path information) of the os program or os script if the job_type is EXECUTABLE.

Start Date: This is the start time for this job. (i.e. When the scheuling of this job would start) Oracle uses the datatype TIMESTAMP WITH TIME ZONE for this column.

End Date: This is the end time for this job schedule. Scheduler will stop running this job after this time. (This also uses the datatype TIMESTAMP WITH TIME ZONE). If not specefied then scheduler will run this job indefinitely, till this job is stopped, dropped or disabled.

Enabled: This parameter tells the scheduler to create the job in disabled or enabled state. By default the job is created as disabled.

Repeat Interval: This specifies how often the job is to be run. With DBMS_JOB we had to use date arithmetic to specify this detail. For example sysdate + 1/24 meant job has to be rerun ever hour. With 10g scheduler it is still possible to use date arithmetic for this purpose. However 10g comes with new calendering expressions where specifying a complex repeat interval is very simple. These expressions are usually made up of two components: frequency and interval. For example, to run a job hourly, instead of using sysdate+1/24 we can use the new calendering expression 'FREQ=HOURLY; INTERVAL=1'. Schedules can be bit more complicated, lets say we want to run a job every Monday and Thursday at 6 PM. We could specify this using date arithmetic 

As you can see it is quite convoluted and, not to say quite confusing, but with new calendering expression specifying complex schedules is piece of cake and not to mention self explainatory.

Wasn't that easier than using date arithmetic? The complete list of frequencies you can specify are yearly,monthly, weekly, daily, hourly, minutely, secondly. The different kinds of intervals you can specify are BYMONTH, BYMONTHDAY, BYYEARDAY, BYDAY, BYHOUR, BYMINUTE, BYWEEKNO and so on. I will talk more about these expressions when I write about named programs and names schedules.

One more thing I would like to say about this is, if you do not specifiy the repeat interval that means you want the job to be run only once. This can be used to run operating system scripts from plsql, for which we had to use java stored procedure in 8i and 9i and dbms_pipe before that.