Specifying the interval in DBMS_JOB |
or "How to use DBMS_JOB without going crazy"
When a DBA or developer is first faced with setting up jobs to run automatically in an Oracle database via the DBMS_JOB package, it can be somewhat intimidating.
Setting up a simple job is fairly straightforward. It can be as easy as this:
variable jobno number;
begin
dbms_job.submit(
:jobno,
'my_proc;',
-- run once a day starting tomorrow
sysdate+1,
-- schedule to run again tomorrow
'sysdate+1'
);
commit;
end;
/
print :jobno
Simple enough, but what if you want to start the job at a certain time on the next day? As it is shown previously, the job will start at exactly 1 day from the time it was submitted. If you submit the job at 1:30 in the afternoon, it will run at 1:30 in the afternoon tomorrow, and 1:30 every day after that.
Actually, it won't run at 1:30 the second day, it will run a little later than 1:30. More on that in a bit.
The trunc() function can be used to force the job to run at a certain time the next day. Keep in mind that the default action for trunc() when passed a date is to truncate to the beginning of the day. So simply use trunc(sysdate), and add the amount of time needed to make the job start when you wish.
But how do you do that?
The key to this is to remember that when adding to a date, it must be done in days, or fractions of days. The easy way to specify fractions of days is to pick the units of time that you want to work with and divide the amount you need into the amount that makes up an entire day.
Say you want to specify a time of 10:00 AM. The following will specify to DBMS_JOB a date and time of 10:00 AM tomorrow.
trunc(sysdate+1) + 10/24
Try doing it in sqlplus:
SQL> select trunc(sysdate+1) + 10/24 from dual;
TRUNC(SYSDATE+1)+10 ------------------- 12/29/2003 10:00:00
Now you see how to specify the time for the job to run initially. Working with our previous example, if you want it to run at 3:30 AM, you could specify it like this:
variable jobno number;
begin
dbms_job.submit(
:jobno,
'my_proc;',
-- run once a day starting tomorrow
trunc(sysdate+1) + 3.5/24,
-- schedule to run again tomorrow
'sysdate+1'
);
commit;
end;
/
print :jobno
This will cause your job to start running the next day at 3:30 AM.
That will work fine, but as you monitor the job start times by querying the DBA_JOBS view, you will find that it starts a little later each day. If your job takes 5 minutes to complete, you will see that after 20 days it is running about an hour later, or at 4:30 AM rather than 3:30.
This is due to the scheduler waiting for the completion of the job before scheduling the next iteration. This is easy to deal with however, just specify the date and time for the interval using the same method as you did for the start time. Using the same example, the following submission to DBMS_JOB will ensure that the job starts at 3:30 AM each morning.
variable jobno number;
begin
dbms_job.submit(
:jobno,
'my_proc;',
-- run once a day starting tomorrow
trunc(sysdate+1) + 3.5/24,
-- schedule to run again tomorrow
'trunc(sysdate+1) + 3.5/24'
);
commit;
end;
/
print :jobno
From there, you can get rather creative in specifying the interval for jobs submitted to DBMS_JOB. If you wanted a job to run at 3:00 in the morning on only the the 3rd day of the month each month, you could use this interval specifiation, as seen in the following SQL:
select trunc(add_months(trunc(sysdate),1),'mon') + 2 + (3/24) from dual;
An interval that specified that a job should be run on the first Monday following the first of the month could look like this:
select next_day(trunc(add_months(trunc(sysdate),1),'mon') + (3/24),'monday') from dual;
though that is likely not the only way to do so.
With a little practice you will be able to specify the correct interval for your jobs. You may need to get a little creative, but it can usually be done. It may mean submitting your job more than once to meet scheduling requirements; just comment your procedures so you will understand what the code is doing should you need to come back and change it later.