Advanced job scheduling with dbms

Call (800) 766-1884 for Oracle support & training

Advanced job scheduling with dbms_job

Donald K. Burleson

As a STATSPACK expert, I commonly get questions like “How do I submit a dbms_job to execute an hourly statspack.snap from 6 AM - 11 PM, Monday through Friday? Is this beyond the capabilities of the DBMS_JOB package?”

As a quick review, the dbms_job.submit procedure accepts three parameters, the name of the job to submit, the start time for the job, and the interval to execute the job:

dbms_job.submit(    what=>'statspack_alert.sql;',
   next_date=>sysdate+1/24, -- start next hour
   interval=>'sysdate+1/24'); -- Run every hour

The problem with this procedure is that while we specify the initial start time and re-execution interval, we do not see a mechanism for running the job during predetermined hours during the day. For example, how can we start the job at 8:00 AM, run it hourly, and then stop at 5:00 PM?

The example about will schedule the job to run hourly, but to get time intervals, it is necessary to create two other jobs, one to “break” the job at 5:00 PM and another to un-break the job the following morning at 8:0 AM.

For advanced scheduling purposes we can create customized intervals such that start and stop at specified intervals. Here are some working examples:

-- Schedule a snapshot to be run on this instance every hour

variable jobno number;

variable instno number;

begin

select instance_number into :instno from v$instance;

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

-- Submit job to begin at 0600 and run every hour

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

dbms_job.submit(

:jobno, 'statspack.snap;',

trunc(sysdate)+6/24,