 |
 |
 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
|
|
 |
|
|
|
 |
|
 |
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, 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); -- ------------------------------------------------------------ -- Submit job to begin at 0900 and run 12 hours later -- ------------------------------------------------------------ dbms_job.submit( :jobno, 'statspack.snap;', trunc(sysdate+1)+9/24, 'trunc(SYSDATE+12/24,''HH'')', TRUE, :instno); -- ------------------------------------------------------------ -- Submit job to begin at 0600 and run every 10 minutes -- ------------------------------------------------------------ dbms_job.submit( :jobno, 'statspack.snap;', trunc(sysdate+1/144,'MI'), 'trunc(sysdate+1/144,''MI'')', TRUE, :instno); -- ---------------------------------------------------------------- -- Submit job to begin at 0600 and run every hour, Monday - Friday -- ---------------------------------------------------------------- dbms_job.submit( :jobno, 'statspack.snap;', trunc(sysdate+1)+6/24, 'trunc( least( next_day(SYSDATE,''MONDAY''), next_day(SYSDATE,''TUESDAY''), next_day(SYSDATE,''WEDNESDAY''), next_day(SYSDATE,''THURSDAY''), next_day(SYSDATE,''FRIDAY'') ) +1/24,''HH'')', TRUE, :instno); commit; end; /
|
|
