3 Replies Latest reply: Apr 1, 2014 1:09 PM by spajdy RSS

    DBMS scheduler - restricting one job run


      Hi All,

      I have a specific question on Oracle DBMS_SCHEDULER and need guidance from the forum. Database version is


      I scheduled a job which is supposed to run every day at 11pm. This scheduler calls a procedure for data movement. The execution time for the procedure is not known (depends on data).


      My question is, if day 1 job keeps running continuously well over 25 hours, will 2nd day job will get triggered ?


      What are the options for ensuring at any time, only one job is running. As per procedure logic, there should not be anytime more than one execution of this procedure.


      Thanks in advance

        • 1. Re: DBMS scheduler - restricting one job run



          If the next schedule of the job happens to be while the job is still running (from the previous schedule), then the new instance of the job will start as soon as the previous instance completes.

          For example:

          - First instance of the job : 13/03/2014 at 11pm


          The job takes 25 hours to complete : 15/03/2014 12 am (midnight) --> second instance of the job starts at this date.

          • 2. Re: DBMS scheduler - restricting one job run



            You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references.

            Immediately after a job is started, the repeat_interval is evaluated to determine the next scheduled execution time of the job. The next scheduled execution time might arrive while the job is still running. A new instance of the job, however, will not be started until the current one completes.

            For more information, please refer to :

            Setting the Repeat Interval






            • 3. Re: DBMS scheduler - restricting one job run

              It depends on how you define repeat_interval.

              There are two formats:

              1. PL/SQL syntax
              2. calendar syntax

              PL/SQL syntax is old one known from dbms_job/dba_jobs. It is simple PL/SQL formula returning DATE. You can imagine that ORACLE evaluate it by SELECT <repeat_interval> from dual.

              In this case next_date is evaluated at job run end. So in you case job start in day D end in D+1 and next_date will be D+2. I suppose this formula trunc(sysdate+1)+23/24.


              When calendar syntax it is used then Scheduler "create" a schedule = list of start dates. You can use dbms_scheduler.evaluate_calendar_string to get this list.

              Formula: FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSEDCOND=0.

              When job start in day D and ends in D+1 after 11pm job attribute schedule_limit affect what happen next. If it IS NULL then because run D+1 11pm was missed job is started immediately. If it is set than it is added to missed run start and compared with actual date and time.

              If actual time is lower than D+1 11pm + schedule_limit job is run immediately otherwise this run is skipped and next run is scheduled to D+2 11pm.


              So if you can control job behavior in this way you must use calendar syntax for repeat_interval and set schedule_limit attribute of job.