Run Job every five minutes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Run Job every five minutes

  1. #1
    Join Date
    Mar 2001
    Posts
    71
    Hi,

    I would like to run job every five minutes(e.g. 00:05, 00:10, 00:15,.....) using DBMS_JOB. I set next_date parameter to

    trunc(sysdate)+1 for tomorrow midnight.

    For interval I can user sysdate+5(24*60). But, it will
    change every time job runs. Is there a way to get exact
    5 minute interval?

    thanks for your help

    np70

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    select to_char(trunc(sysdate,'mi')+1/288, 'dd-mon-yyyy hh24:mi:ss') from dual
    /

  3. #3
    Join Date
    Mar 2001
    Posts
    71
    Pando,

    thank you for reply. Could you tell me what 1/288 is for?
    Also, should I just put

    to_char(trunc(sysdate,'mi')+1/288, 'dd-mon-yyyy hh24:mi:ss')
    as interval parameter of dbms_job.submit procedure?

    thanks,

    np70

  4. #4
    Join Date
    Mar 2001
    Posts
    71
    Pando,

    I figured what 1/288 is for. It is 5/(24*60). But
    this value is valid only if we assume job finished within
    minute. If job takes more than One minute, next schedule
    time will be incorrect.

    e.g.
    00:00 --> first Job (took 30 seconeds)
    00:05 --> second job ( took 1 minute and 30 seconds).


    so next time will be

    00:11 -->third job

    Is there a way to get next interval without using current
    time?

    thanks

    Np70

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    it should execute every 5 minutes because next_date in user_jobs is set once the job starts not when the job is finished

    I can test it because I dont have a job that takes longer than 1 minute


    [Edited by pando on 09-25-2001 at 12:36 PM]

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    it works, I tried it

    Code:
    connect internal
    grant execute on dbms_lock to lsc:
    connect lsc
    
    create or replace procedure sleep
    as
    begin
    dbms_lock.sleep(90);
    end;
    /
    
    variable job number
    
    begin
    dbms_job.submit(:job, 'sleep;', sysdate, 'trunc(sysdate,''mi'')+1/288');
    end;
    /
    
    commit;
    
    select next_date from user_jobs;
    next_date
    ------------
    08:14:02
    
    select sysdate from dual;
    08:14:23
    
    exec dbms_job.run(4);
    
    sleeps 90 seconds............
    
    select next_date from user_jobs;
    next_date
    -----------
    08:19:00
    
    wait for 5 minutes for next execution
    
    08:19:00 job starts
    
    sleeps 90 seconds............
    
    select next_date from user_jobs;
    next_date
    -----------
    08:24:00

    it is scheduled for every 5 minutes even the jobs takes longer than a minute

  7. #7
    Join Date
    Mar 2001
    Posts
    71
    pando,
    thank you. now i can see that next interval is calculated
    from start time and not the end time.

    again thanks

    np70

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