Re: How to set the job's interval

Code a function (ie Next_Refresh_Date_Time) to calculate the interval based on your defined logic. In the function, manipulate sysdate and return trunc(sysdate) + n/1440. This logic allows you to skip intervals (don't run on Sunday, don't run between 01:00 and 03:00, etc) if required.

You can also code for gaps is processing. If the job starts to run for 14 minutes elapsed time, do you still want it to execute on the 15? Whatever your answer, it is back in your control.

I would also ensure whatever is getting executed by DBMS_JOBS does not fail. Code a wrapper package to handle and/or report all errors but don't let the job fail because you loose control over the next execution and the job will break after 16 consecutive failures (must somehow be successfully executed again).

Then you are only left with the job possibly executing at startup (if the next_date has pasted) and this will probably not be on the 15. One execution at startup doesn't sound too bad.

HTH
Dave.

Winnie_Liu_at_infonet.com on 06/13/2000 04:49:59 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Dave Ryan/ITC/SWP)

Subject: Re: How to set the job's interval

For just one (or several) jobs per day, I also use trunc(sysdate)+n to schedule the jobs. But if I want it to run on every 30 minutes, there is almost no way to make the job scheduled so it will start at the exact time that I want.

But I admit that cron do have some draw back. Of course it required the system and the database to be up (of course the system had to be up! The database is running on the system!!! :P) But it do allow a greater flexibility in dealing with scheduling.

I hope Oracle can figure out a way which can allow us to schedule jobs like cron. Maybe in Oracle 10!!

Winnie
:)

"Charlie Mengler" <charliem_at_mwh.com> on 06/13/2000 02:13:21 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Winnie Liu/HQ/ISC)

I am NOT claiming this is an "elegent" solution but it does work reliably for me.

I use "trunc(sysdate)+7+20/24" for a LONG running DBMS_UTILITY.ANALYZE_SCHEMA run
every Sunday starting at 8PM. Even though it takes 4 - 5 hours to complete, it
always starts at 2000 hours on Sundays reliably.

Also I I have a problem with the use of cron, which actually "bit me" last night.
Needless to say for the cron job to succeed the system & the data base needs to be up.
If either is down at the appointed time, the job simply does not run. With DBMS_JOB,
if the instance is down at the schedule time to run, the job will be started when
the instance is brought up. Of course this could have some problems, too. ;-)

So pick your poison & be aware of the limitations of your choice.

HTH & HAND! Winnie_Liu_at_infonet.com wrote:
>
> But the problem for this is, if the job run @15th minute for about 5
> minutes, then the next time it will start @35th minutes for another 5
> minutes, then the time after that will be @55th minute etc...
>
> If he does not mind that, then the formula will work.
>
> I still find that the best way to make the database to run the job in a
> particurlar time and interval is CRON!
>
> Winnie :)
>
> dgoulet_at_vicr.com on 06/13/2000 07:31:25 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc: (bcc: Winnie Liu/HQ/ISC)
>
> The DBMS_JOB submit and next_date procedures require a character string
> that it
> can evaluate into a date. Therefore all of the tricks you know for
> converting
> data into dates works. In your case try the following:
>
> "sysdate+(15/60)/24"
>
> The point is to get the job to start at the correct time.
>
> ____________________Reply Separator____________________
> Author: cyhu_at_eachnet.com
> Date: 6/12/00 6:58 PM
>
> Hi,can anyone tell me how to set the job's interval so that the job can
run
> at 15,30,45,60 minutes every hour.
> Thanks in advance.
>
> -------------------------------
>
> http://www.eachnet.com
> Tel(O)??86-21-52340871/0872*667
> E-mail??cyhu_at_eachnet.com
> ??Eachnet.com - CHINA's LARGEST ON-LINE CONSUMER TRADING COMMUNITY??
>
> -------------------------------
>
> --
> Author:
> INET: dgoulet_at_vicr.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Author:
> INET: Winnie_Liu_at_infonet.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Charlie Mengler                               Maintenance Warehouse
charliem_at_mwh.com                              5505 Morehouse Drive
858-552-6229                                  San Diego, CA 92121
Always be sincere about your enthusiasm, whether you mean it or not.
--
Author: Charlie Mengler
  INET: charliem_at_mwh.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






--
Author:
  INET: Winnie_Liu_at_infonet.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L