DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: dbms_job.interval

  1. #1
    Join Date
    Dec 1999
    Posts
    217
    I need to change the interval setting for a job defined in dbms_job. I need for the job to run everyday at 7.00 AM. WHat is the correct interval syntax for it. Is it
    "dbms_job.interval(,'TRUNC(SYSDATE+1)+7/24');"

    APpreciate the input in advance.

    Thanks,
    Chintz

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, your syntax is correct, except that the job# as the first argument is missing in a call.

    dbms_job.interval(123,'TRUNC(SYSDATE+1)+7/24');

    where "123" is the number of your job (job id). Query view USER_JOBS to get your proper job id.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 1999
    Posts
    217

    Unhappy

    I missed the job number in my initial posting. But, yes, I do understand that I need to put the job# there. But when I execute that command and query the dba_jobs table for that job the next_sec field is set at 00:00:00, whereas it should be 7:00:00.
    So, it makes me feel there is something wrong with the syntax, 'cause the system should calculate and display the next time the job needs to be run.

    Chintz

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Have you issued a COMMIT after the call to DBMS_JOB.INTERVAL()? If you are querying your user_jobs from another session, you won't see the changes untill you commit them. And of course, the changes will also not be visible to the dbms_job scheduler (ie will not take effect) untill you commit them.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 1999
    Posts
    217
    Yes, I did do a commit.

    Chintz

  6. #6
    Join Date
    Feb 2001
    Posts
    389
    Hi,

    The interval is the interval between the job run and next run time.
    You cannot set the time it should run but after how much time it should run after job execution.
    So if you want it to run at 7AM ,u have to execute it at 7AM
    and then give trunc(sysdate) + 1.

    I hope it is clear.

  7. #7
    Join Date
    Dec 1999
    Posts
    217
    There must be a way of running a job at a certain time. If I wanted to set up the job to run at 7.00 AM everyday how would I do it?

    Chintz

  8. #8
    Join Date
    Feb 2001
    Posts
    389
    Actually your syntax should have worked,
    Try two things:

    stop job queue processes and restart them.

    Or else remove old job and resubmit with new interval.

    Hope it helps.

  9. #9
    Join Date
    Dec 1999
    Posts
    217
    How do you stop the job process?
    Also, should I just do a dbms_job.remove & then a dbms_job.submit?

    Chintz

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by gpsingh
    Hi,

    The interval is the interval between the job run and next run time.
    You cannot set the time it should run but after how much time it should run after job execution.
    So if you want it to run at 7AM ,u have to execute it at 7AM
    and then give trunc(sysdate) + 1.

    I hope it is clear.
    You are wrong. If you set the job to run today at 7AM and set the interval to 'trunc(sysdate) + 1', your job will run at midnight every day, not at 7AM. You could set the interval to 'sysdate+1', which means it will run tomorow and any other day at *approximately* 7AM. Why approximately? Today it might happen to execute 30 seconds past 7AM. So when the job finishes today, tje next execution date will be set at tomorow at 07:00:30AM. Again, tomorow the job scheduler might be late for another 30 seconds, resulting at execution time at 07:01:00AM. And so on and on, the start time of the job will slowlly be shifted and in a couple of months you'll find it starting at 8AM instead of at 7AM.

    To avoid this, you have to "anchore" the next starting time of the job, which is exactly what the chintz's original call does: "dbms_job.interval(xyz,'TRUNC(SYSDATE+1)+7/24');" This ensures that the job will be executed at 7AM every day. It might actualy start a couple of secunds later (depending on your JOB_QUEUE_INTERVAL setting), but the the time offset will not accumulate!

    Chintz, again, your syntax is perfectly correct, I'm 100% sure. You say you are seeing NEXT_SEC field as set to 00:00:00. But this is normal! You have only set the new interval, not the new time of your first next execution time. To change your next execution time, you must use DBMS_JOB.NEXT_DATE(xyz,TRUNC(SYSDATE)+14/24); if you want to execute it today at 2PM for example. But tomorow it will be run at 7AM!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width