Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HELP: DBMS_JOB QUESTION

Re: HELP: DBMS_JOB QUESTION

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 21 Sep 1999 11:41:49 -0400
Message-ID: <EI3nNxi4KZZ+PCQJzW3++bRFQyG8@4ax.com>


On Tue, 21 Sep 1999 16:41:22 +0900, Huiming Li <huiming_at_jis.ne.jp> wrote:

>Hi, everybody,
>
>I expect my job to execute automatically every 15 minutes (exactly!).
>Is there a way?
>
>The following is what I have done and the result.
>
>Initialization parameters:
> job_queue_processes = 1
> job_queue_interval = 60
>
>execute dbms_job.submit(:jobno, 'XYZ;', SYSDATE, 'SYSDATE + 1/96');
>
>what I got:
>
> The 1st Time 15:40:13 (expected time 15:40:00)
> The 2nd Time 15:55:40 (expected time 15:55:00)
> The 2nd Time 16:11:20 (expected time 16:10:00)
> ....
>
>It seems that the error is getting larger and larger.

There is no error. Your job queue interval is 60 seconds. So what that means is that every 60 seconds it wakes up, checks the queue for jobs to run, runs those that need running, and goes to sleep for a minute. If it takes 3 minutes for the jobs to run then it will sleep and wake up a minute later, really taking 4 minutes to check the queue again. This will cause your slipping start time. Since the interval time is determined before the job runs, you need to come up with some formula, that takes the slipping start time into account. Something like this should do for an interval assuming your initial start time was on the 00th, 15th, 30th,

or 45th minute...

to_date( to_char( sysdate, 'DDMMYYYYHH24' ) || '0000',

         'DDMMYYYYHH24MISS' )
     + ( trunc( to_char(sysdate,'MI')/15 ) * 15 + 15 )/1440

This will not guarantee that the job will start exactly at that time, but it will schedule it to start at the next appropriate time.

hope this helps.

chris.

>
>Thanks in advance.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 21 1999 - 10:41:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US