5 Replies Latest reply on Apr 10, 2014 2:48 PM by GregV

    How to Schedule Jobs to only run during a time window

    jeff.blankenbiller

      I have a long running task that needs to schedule jobs to process data.

      I only want these scheduled jobs to start during a specific window of time each day, probably 10:00 PM to 6:00 AM.

       

      If the scheduled jobs do not begin during the specified time frame, they must wait until the next day to start running.

       

      Each scheduled job will only be executed once and then auto dropped.

       

      How should I go about creating these scheduled jobs?

        • 1. Re: How to Schedule Jobs to only run during a time window
          GregV

          Hi,

           

           

          You can create a window and assign that window to your jobs.

          However, if they are meant to be one-off jobs, why not pass their start_date as the date you want them to run?

          • 2. Re: How to Schedule Jobs to only run during a time window
            jeff.blankenbiller

            I am unfamiliar with how windows work. From reading Oracle documentation, it sounds like they are used to change the Resource plan that are active during the window time frame.

             

            I don't understand how to make the tie this into the jobs so that they only begin during the window.

             

            Yes, you could say that these are one-off jobs, but there will be hundreds of thousands of them.  Some complete in minutes but others take hours.

            Since there are so many and I can't be sure when they will begin running, I need to schedule them in such a way that the scheduler will pick them up and only begin running them if it is in the window time frame.

             

            If a Window would work for this, could you provide an example of how to create the Window and how to schedule the job to only run during the Window?

            As an example, a 10 minute window could be used for dev/testing.

            • 3. Re: How to Schedule Jobs to only run during a time window
              GregV

              Hi Jeff,

               

              I agree that the documentation isn't clear enough about the purpose of windows.

              You can indeed use windows for changing the resource plan, but you can also use them for scheduling your jobs.

              I did a simple test in real-time to illustrate the latter.

               

              At around 10.30 am today I created a table that will populated by a job:

              CREATE TABLE TEST_WINDOW_TABLE(EVENT_DATE DATE);

               

              Then, I created a window whose start_date is today at 10.40 am :

               

              dbms_scheduler.create_window(

                                           window_name     =>'TEST_WINDOW',

                                           resource_plan   => NULL,

                                           start_date      => to_date('10/04/2014 10:40:00', 'dd/mm/yyyy hh24:mi:ss'),

                                           repeat_interval => NULL,

                                           duration        =>interval '5' minute

                                          );

               

              You can see that this window doesn't have a resource plan, and its repeat interval is NULL (so it will be opened only once).

              The window will stay open for 5 minutes.

               

              Finally, I created a one-off job whose schedule is the previously created window:

               

              DBMS_SCHEDULER.create_job (

                                         job_name      => 'TEST_WINDOW_JOB',

                                         job_type      => 'PLSQL_BLOCK',

                                         job_action    => 'BEGIN insert into test_window_table values (sysdate); COMMIT; END;',

                                         schedule_name => 'SYS.TEST_WINDOW',

                                         enabled       => true,

                                         auto_drop     => true

                                        );

               

              Checking the user_scheduler_job_log before 10.40 would return no rows, which mean the job hasn't started yet since the window was not open.

               

              Now, from 10.40, it shows one entry:

               

              SQL> select log_date, status from user_scheduler_job_log where job_name = 'TEST_WINDOW_JOB';

               

              LOG_DATE                                                                         STATUS

              -------------------------------------------------------------------------------- ------------------------------

              10/04/14 10:40:02,106000 +02:00                                                  SUCCEEDED

               

              The TEST_WINDOW_TABLE has also got the row:

               

              SQL> select * from TEST_WINDOW_TABLE;

               

              EVENT_DATE

              ------------------------------

              10/04/2014 10:40:02

               

               

              Voilà.

               

              In your case, since you want to run the jobs daily between 10 pm and 6 am (duration of 8 hours), the window would look like this:

               

              dbms_scheduler.create_window(

                                           window_name     =>'YOUR_WINDOW',

                                           resource_plan   => NULL,

                                           repeat_interval => 'freq=daily;byhour=22;byminute=0;bysecond=0',

                                           duration        =>interval '8' hour

                                          );

               

              For your jobs, you may need to specify an end_date if you want to make sure the job gets dropped if it couldn't run in its window.

              • 4. Re: How to Schedule Jobs to only run during a time window
                jeff.blankenbiller

                Thanks for the reply Greg.

                 

                This is an excellent example of exactly what I needed.

                 

                I will add that that the only change I needed to make was that since these are one-off jobs that should be auto-dropped after running, that I had to set the MAX_RUNS attribute to 1 on each job so that it will be dropped after completing.

                Otherwise, the job would have been executed again due to the fact that the Window has a REPEAT_INTERVAL.

                 

                This should be added to Oracle documentation as an example.

                • 5. Re: How to Schedule Jobs to only run during a time window
                  GregV

                  Thanks.

                  Scheduler is such a nice and powerful feature, but there are many lacks in the documentation. There are a couple of notes on My Oracle Support that provide more details on some points, but you need a support contract with Oracle to access them.

                  I recently created an SR to file a documentation defect about the Exclude option of the Scheduler syntax. It's not much but I'm trying to contribute to improving the Oracle documentation, which I find nevertheless excellent and overall well made.