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: Strange dbms_job problem

Re: Strange dbms_job problem

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/05
Message-ID: <8hgkv4$89r$1@nnrp1.deja.com>#1/1

In article <8hfn22$ing$1_at_nnrp1.deja.com>,   sascha_kiefer_at_my-deja.com wrote:
> Hi,
>
> I tried to creat a job, that runs every hour.
> I used the following statements:
>
> variable :jobnr number
>
> begin
> dbms_job.submit

 (:jobnr, 'Pik_Bbm_Ebna_Utils01001.CreateYesterdayLog;',
> sysdate, 'sysdate+1');
> commit;
> end;
>
> normally it should run directly and then every hour.
> In the table user_job the entry is correct, but the job dosen't work.
>
> When I use dbms_job.run (22) the program does what I expect.
>
> What's wrong ?
>
> Thanks
> Sascha
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

you meant day not hour (i saw that in another followup -- you did mean every day)... So, i would ask you:

did you set the init.ora parameters job_queue_processes and job_queue_interval? If you did not, then jobs do not run by themselves....

Also, you might consider using a NEXT like:

'trunc(sysdate) + 1 + 8/24'

for example to get a job to run every day at 8am. Your specification of

'sysdate+1'

will suffer from "time creep" over a couple of runs. Jobs do not run at exactly the moment you specify, but near the moment in time. If you submitted the job say at 8am one morning, you might find that it runs at:

8:00 the first time
8:01 the next day
8:02 the next day
8:04 .... and so on

that the time creeps up. Thats cause the interval is computed with the job is run and the job will run "soon after" its desired start time. Its best to use functions that are not relative to the CURRENT time (eg: by doing the trunc(sysdate) + 1 + 8/24, we went to tomorrow at midnight and added 8 hours -- the job will always be scheduled for tomorrow at 8am)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Jun 05 2000 - 00:00:00 CDT

Original text of this message

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