Skip to Main Content
  • Questions

  • Schedule job "B" to run after job "A" completes and prevent job "A" from running again until job "B" completes.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Eric.

Asked: April 12, 2017 - 3:07 pm UTC

Last updated: April 12, 2017 - 3:32 pm UTC

Version: 11gr2

Viewed 1000+ times

You Asked

AskTom,

I have an Oracle Scheduled job called "A" that runs every 5 minutes and takes 10 seconds to complete but could run longer. I have a second job called "B" that I want to run at 1 am daily. I don't want job "B" to run at the same time as job "A" so I need a way to make job "B" wait for job "A" to complete, disable job "A", run job "B" and if job "B" completes, enable job "A" (breathe). I don't know how long job "B" will take to complete because its run duration varies from 1 second to 3 hours depending on how much data it needs to process. If Job "A" fails, Job "B" is still allowed to run. If Job "B" fails, it still enables Job "A" to run.

A simpler way of saying this is that Job "A" must wait for Job "B" to complete and Job "B" must wait for Job "A" to complete.

How can I do this?

Example:

12:50 am - Job A runs and completes
12:55 am - Job A runs and completes
01:00 am - Job A runs and completes
01:00 am - Job B runs and waits for Job A. If Job A completes, Job A is disabled and Job B runs.
01:33 am - Job B completes, Job A is enabled.
01:35 am - Job A runs and completes
01:40 am - Job A runs and completes
01:44 am - Job A runs and completes

NOTE: We are strongly encouraged to use Oracle Scheduled Jobs (dbms_scheduler) and avoid dbms_job.

and Chris said...

So you should be strongly encouraged to user the scheduler: you can do this easily with it using chains.

You use these to define a series of actions to take and the rules which govern when the next step starts.

You can read about these in the docs at:

https://docs.oracle.com/database/121/ADMIN/scheduse.htm#GUID-BF3AB6EB-BC19-4303-9E02-6466804BA119

And here's a worked example:

https://oracle-base.com/articles/10g/scheduler-enhancements-10gr2#job_chains

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_SCHEDULER

More on PL/SQL routine DBMS_SCHEDULER here