4

It's probably just the vocabulary I am missing to find out how to do this:

A job scheduled to run regularly every 5 mins, however keeping track that there are never two instances of it running at the same time, i.e. the next instance would be postponed or skipped if the prior runs longs than 5 mins.

What is the easiest/most elegant way to achieve this?

Vadzim
  • 24,954
  • 11
  • 143
  • 151
PeterP
  • 4,502
  • 7
  • 22
  • 21

4 Answers4

4

From the Oracle 10g administrators guide:

"The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package. In particular, if you are administering jobs to manage system load, you should consider disabling DBMS_JOB by revoking the package execution privilege for users."

DBMS_SCHEDULER is Oracle's recommended way to do this now. One advantage it has is that you can manage your jobs via Enterprise Manager/Grid Control if you're using this.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • Still Tom Kyte prefers DBMS_JOB for simple cases. See http://stackoverflow.com/questions/4152111/dbms-job-vs-dbms-scheduler. – Vadzim Oct 15 '14 at 10:06
3

DBMS_JOB takes care of that. Just use it.

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
  • +1 for not re-inventing the wheel. Also, see the DBA_JOBS and DBA_JOBS_RUNNING views if you need to determine the last/next run information. – dpbradley Jun 25 '09 at 12:10
  • 1
    thanks a lot for your response. does it take care of avoiding two instances of the job running at the same time, too? if yes, which option should i use for this? this is what i did not manage to find out yet... – PeterP Jun 25 '09 at 15:49
  • 2
    IIRC, dbms_job will only run one at a time (you won't have two instances of a job running). Dbms_scheduler allows greater flexibility as to what to do on job failure. – DCookie Jun 25 '09 at 17:34
1

an other advantage that dbms_scheduler has above dbms_job is that you can better control the load, resource usage and that you can also run jobs external to the database.

hth, Ronald.

-1

If for some reason dbms_job or dbms_scheduler doesn't work for you, you could also use DBMS_APPLICATION_INFO.SET_APPLICATION_INFO to set the module name of your job and you could query v$session to see how many sessions are currently executing that module.

What type of job is this? A PL/SQL stored procedure?

Plasmer
  • 1,110
  • 5
  • 10
  • thanks for your reply, I think both of dbms_job and dmbs_scheduler would work for me, but somehow I do not seem to be smart enough to figure out how to avoid two jobs overlapping... – PeterP Jun 25 '09 at 15:46
  • btw: it's a stored procedure, yes – PeterP Jun 25 '09 at 15:47
  • You don't need to worry about jobs overlapping. Both scheduling mechanisms will only run one instance of a job at a time. – DCookie Jun 26 '09 at 01:54
  • -1 Sorry, this just won't work - in the time between when you query v$session, then call SET_APPLICATION_INFO, there's nothing stopping another process doing the same thing - you'll still end up with multiple sessions with the same APPLICATION_INFO. If the dbms_job/dbms_scheduler doesn't cut it, DBMS_LOCK does provide a way to create locks. – Jeffrey Kemp Jun 26 '09 at 01:58
  • Yes, this does not work well when there is a lot of concurrency, but if the job is only called every 5 minutes as the questioner mentioned, I don't believe this is a problem. – Plasmer Jun 26 '09 at 16:47