Running dbms_scheduler within a timing interval

To run a statspack snap within a scheduled timing , you can use the below, it will start the job at 8 am and run until 8pm from monday until friday.


exec dbms_scheduler.create_job(JOB_NAME => 'JOB_STATSPACK_SNAP',job_type=> 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN STATSPACK.SNAP; END; ', START_DATE => trunc(sysdate) + 8/24 , REPEAT_INTERVAL => 'FREQ=HOURLY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19,20', ENABLED => TRUE, AUTO_DROP =>FALSE, COMMENTS =>'Statpacks snap') ;

another example , would be below running in between the time 8am til 8pm with interval 15 mins


exec dbms_scheduler.create_job(JOB_NAME => 'JOB_STATSPACK_SNAP',job_type=> 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN STATSPACK.SNAP; END; ', START_DATE => trunc(sysdate) + 8/24 , REPEAT_INTERVAL => FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19;BYMINUTE=0,15,30,45', ENABLED => TRUE, AUTO_DROP =>FALSE, COMMENTS =>'Statpacks snap') ;

To check if your calendar string is correct , you can use below to do this.


declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
L_RETURN_DATE := L_START_DATE;
for ctr in 1..100 loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,20',
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line('Next Run on: '
to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
);
l_return_date := l_next_date;
end loop;
end;
/