Friday, February 12, 2010

dbms_job interval time meaning and on how to set interval in dbms_job in pre- 10g databases.

Its been a month having not written a new post in my blog and thought I could post a message to keep my blog update to date and even if it is a trivial info. This post came as outcome of a discussion with a friend on how to change interval time in earlier version of oracle db and here is the post about it with few other additional info.

Simple technique to schedule jobs in oracle 10g is explained in earlier post but sometimes we may need to work with pre-10g Oracle databases which do not provide a mechanism to schedule jobs as we do in 10g database or above.

Just for the purpose of completion, I would show how we can create a scheduler job in earlier database (not from 10g onwards) is as follows

testjob number;
dbms_job.submit(job => testjob,
what => 'procedure1(1);'
next_date => sysdate+1,
interval => 'sysdate+1');
The simple job creation demo has two date/time related parameters and a procedure execution call. The date related parameters in this call are next_date and interval and which are date datatype or the values we pass can be varchar or char but they should be in such away that they would be evaluated to a date format later. Next_Date should always be greater than or equal to sysdate.

This post especially written to help in setting next_date parameter value of dbms_job.sumbit_job procedure and thought it will help you all as well as it helps me when needed. How to change REPEAT_INTERVAL of scheduler job in oracle 10g can be seen .

This post is all about how to set interval which meets the various conditions or time set values at which we want job to be executed. The calculations are very simple arithmetic.

SELECT SYSDATE,sysdate + 1/(60*60*24) AS SYSDATE_1SECONDAFTER , sysdate + 1/(60*24) AS SYSDATE_1MINUTEAFTER , sysdate + 30/(60*24) AS SYSDATE_30MINUTESAFTER,

The above query is just to show how we can pass date time value to interval and you may use any one the listed in query incase suits.

Few more examples:
Sysdate+1 – same time but tomorrow
Trunk(sysdate+1) – tomorrow, today midnight i.e. just before starting of new day , we can say 12 AM;
trunc(sysdate)+5/24 – 5 am in the morning.

Interval parameter is incremented on each job run and you may set NULL to interval if job should not run any further at all.

DBMS_JOB.CHANGE procedure can be used to change parmaeter values of job.

Final say: go for dbms_scheduler for creation on new jobs and it is rich in all aspects.

Thank for reading and welcome your posts as all the time.

No comments:

Post a Comment