How many of us have had to schedule a job to run inside an Oracle Database? I believe this is one of the most common task that a DBA performs. But, some time the request doesn't end there, how about if the job is longer that we think? how about if we only want that job to run inside an period of time regardless if the job completed all the work or the job only completed 50%, 40% of the work? In order to comply that we have to play with some objects inside Oracle Database called: Schedules, Windows, Programs, Procedures, Jobs and Jobs attributes. This is what I will teach you in this articles. We will create a produce that will be executed by a Job only inside a period of time, if the procedure is longer than that period of time the job will be stopped and re-started in the next run, as a result the procedure will be stopped too. Let me show you the concept of the objects that we will work with, directly from Oracle Documentation:
Procedure: A procedure is a group of PL/SQL statements that you can call by name. A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. For our case, The code that will be executed with the Job. Read more here.
Program: A program is a collection of metadata about a particular task. Read More here.
Job: A job is the combination of a schedule and a program, along with any additional arguments required by the program. Read more here.
Job Attribute: If the program that the job executes uses input variables, then we can set the value of those attributes using "Job Attributes".
Schedule: A schedule defines when a job should be run or when a window should open. Read more here.
Window: Windows provide a way to automatically activate different resource plans at different times. Read more here. When we create our "Window", we will create it with priority "HIGH". There may be only one "Window" Open at the same time, so all the other windows that have "LOW" priority will be stopped and our "Window" will be open. For example in the following image, between 6:00am and 9:00am there is already other Window Open, the "Window 1", however since the "Window 3" as "HIGH" priority, the "Window 1" is closed and the "Window 3" is open. When the "Window 3" completes the "Window 1" will be re-open at 9:00am because the duration is longer than "Window 3".
So let's start. The goal of this article will be to run the procedure "dgomez.myprocedure" only on the days Saturday and Sundays and only between 9:00am and 12:00pm. Regardless if the procedure completes or not, the procedure will be stopped at 12:00pm.
We will go through the following Steps:
I will leave the logic empty, since what matters here is the structure to build a procedure that run only inside a specific period of time. The idea is that you create your procedure as you want. I will use only one input variable (var1) but you can use as many as you need.
SQL> CREATE OR REPLACE PROCEDURE dgomez.myprocedure (var1 IN NUMBER) AS BEGIN null; --Procedure Logic here.END;/ Procedure created.
Now let's create the "Schedule" where we will say when the procedure will run. You can see the "Schedule" as the answer for the question "When the procedure will start?"
SQL> Begin DBMS_SCHEDULER.CREATE_SCHEDULE ( schedule_name => 'WEEKENDS_SCHEDULE', start_date => NULL, repeat_interval => 'freq=daily;byhour=9;byminute=0;bysecond=0;byday=SAT,SUN', end_date => NULL, comments => 'for Saturday and Sunday, it will start at 09:00 am'); End;/
PL/SQL procedure successfully completed.
We will specify now when the job should start running, this means the start point when the job will be start working.
SQL> BEGINDBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'WEEKENDS_SCHEDULE', attribute => 'start_date', value => TO_TIMESTAMP_TZ('07-25-2016 09:00:00 -04:00', 'mm-dd-yyyy hh24:mi:ss TZH:TZM')); END;/SQL> PL/SQL procedure successfully completed.
Next step is to create a "Window", here is where we answer the question "How long will run?". Since we said that our procedure should start at 9:00am and end at 12:00pm , the duration should be 3 hours.
SQL> Begin DBMS_SCHEDULER.CREATE_WINDOW ( window_name => 'MY_WEEKENDS_WINDOW', resource_plan => NULL, schedule_name => 'WEEKENDS_SCHEDULE', duration => '0 03:00:00', window_priority => 'HIGH', --If there is another window active, this will replace it. comments => 'The job will run for a period of 3 hours, from 9:00am to 12:00pm'); End;/
We are creating here a Window Group just to follow the recommendation from Oracle. This is optional but useful. Useful when you have several Windows for example, one window for weekdays or window for every day of the week; and another for weekends.
SQL> Begin DBMS_SCHEDULER.CREATE_GROUP ( group_name => 'MY_MAINTENANCE_GROUP', group_type => 'WINDOW', member => 'MY_WEEKENDS_WINDOW', -- You can put here all the Windows that are part of this group. comments => 'This group will include all the windows'); End;/
The program is useful to define our procedure.
SQL> Begin DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'DGOMEZ.MY_PROGRAM', --Just the name of the program. program_type => 'STORED_PROCEDURE', program_action => '"DGOMEZ"."MYPROCEDURE"', number_of_arguments => 1, enabled => false, comments => 'We are defining our procedure, its attributes and state');End;/
SQL> begindbms_scheduler.define_program_argument ( program_name => 'DGOMEZ.MY_PROGRAM', argument_position => 1, -- Pointing to the first input variable argument_name => 'var1', -- first input variable of your procedure argument_type => 'NUMBER', default_value => '10', --just an example of the value for your procedure out_argument => FALSE);END;/
Enabling the Program
SQL> beginDBMS_SCHEDULER.enable (name => 'DGOMEZ.MY_PROGRAM');end;/
Finally we will use a "Job" to say what should be run (DGOMEZ.MYPROCEDURE) and when should be run (MY_MAITENANCE_GROUP windows group):
SQL> Begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'DGOMEZ.MYJOB', program_name => 'DGOMEZ.MY_PROGRAM', schedule_name => 'SYS.MY_MAINTENANCE_GROUP', enabled => TRUE, auto_drop => FALSE, comments => 'Job that runs my procedure on weekends', job_style => 'REGULAR'); End;/
The following attribute is really important, it is what makes this work, it says that the job should be stopped immediately when the "Window" get closed. This is what makes our procedure do not run longer than our period of time.
SQL> BEGINDBMS_SCHEDULER.SET_ATTRIBUTE ( name=>'DGOMEZ.MYJOB', attribute=>'stop_on_window_close', value=> true );END;/
SQL> column owner format a10SQL> column job_name format a15SQL> column job_action format a15SQL> column program_name format a20SQL> column schedule_owner format a10SQL> column schedule_name format a25SQL> set linesize 150SQL> select owner, job_name, job_action, program_name,SCHEDULE_OWNER, SCHEDULE_NAME from dba_scheduler_jobs where owner='DGOMEZ' and program_name='MY_PROGRAM'
OWNER JOB_NAME JOB_ACTION PROGRAM_NAME SCHEDULE_O SCHEDULE_NAME---------- ---------- ------------ -------------- ---------- --------------------DGOMEZ MYJOB MY_PROGRAM SYS MY_MAINTENANCE_GROUP
SQL> select window_group_name, enabled, next_start_date from dba_SCHEDULER_WINDOW_GROUPS where window_group_name='MY_MAINTENANCE_GROUP';
WINDOW_GROUP_NAME ENABL NEXT_START_DATE------------------------------ ----- -----------------------------------MY_MAINTENANCE_GROUP TRUE 30-JUL-16 09.00.00.000000 AM -04:00
Nicely detailed, thakks for the share.