Hello Group,

I have a couple of questions surrounding the Oracle8i job scheduler. First, here's some background info about my problem. I have a very long running pl/sql procedure that needs to execute on a regular basis for the next 4 days. The problem with this procedure is that it performs a complicated select with several outer joins and then has to delete at least 50,000 records returned by the query before committing, and this all has to happen in an EXTREMELY busy production environment. With the long running process and the added mystery of how busy the DB is at different times of the day, it's hard to say how long the process will take to run any given time (last time I ran it manually and it took 4 hours and 5 minutes to complete). Because of the nature of the process, I don't want more than one instance of it running, but there is a serious time constraint as well - 2.7 million records need to be deleted in the next 4 days!

The Oracle8i reference seems to be stating that the NEXT_DATE on a scheduled job is set from the BEGINNING of the execution time (as opposed to when the process completes), so if the process begins at 1PM, the NEXT_DATE is set to 5PM, but I can't find anything about how it is set if the process hasn't completed by 5PM. My assumption is that another 4 hours would be added to the first NEXT_DATE calculation, thus setting the NEXT_DATE to 9PM. If this is the case, then my job (which may only take 5 minutes more than the 4 hour interval) won't run again for 8 hours after the first time it began running, which is a problem with my 4 day time constraint.

So here's what it all boils down to...If I just assume the job will take 4 hours to run on average, and I use the job scheduler to schedule it for every 4 hours, then (based on my assumption) it could potentially be 8 hours before it begins running again. In this case, wouldn't it be wiser to schedule it for a short period of time, like 1 hour, so the next start time is never too long from the last completion time? If my assumption about how the scheduler works is incorrect, then I need clarification about that and suggestions on how to best schedule this routine.

THANKS!

-JMack