Scheduler Job running on Scheduled Holiday, runs 1st instance
I created Schedules with some dynamically generated days and some Static holidays. When I created the job with the above scheduled into EXCLUDE clause. The 1st instance of all the jobs are still running on the scheduled Holiday. How do I stop all the jobs running on an excluded holiday. Here is the example of the schedles/Jobs created. Please help me resolve this issue.
create table temp(runid number, insert_ts DATE , job_name varchar2(30));
create sequence runids start with 1 increment by 1;
DBMS_SCHEDULER.create_schedule (
schedule_name => 'ColumbusDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=OCT;BYDAY=2MON;',
comments => 'Columbus Day'
);
DBMS_SCHEDULER.create_schedule (
schedule_name => 'Thanksgiving',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=-1THU;',
comments => 'Thanksgiving'
);
DBMS_SCHEDULER.create_schedule (
schedule_name => 'FederalHolidays',
repeat_interval => 'ColumbusDay,'
|| 'Thanksgiving;',
comments => 'Federal Holidays'
);
DBMS_SCHEDULER.create_schedule (
schedule_name => 'SCH_HOL',
repeat_interval => 'FREQ=YEARLY;BYDATE=20110101,20111107;',
comments => 'Holidays Declared by as Static only on Demand'
);
DBMS_SCHEDULER.create_schedule (
schedule_name => 'SCH_Holidays',
repeat_interval => 'FederalHolidays,SCH_HOL;',
comments => 'Public Holidays for every Year'
);
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'EMAIL',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
Insert into temp(runid, insert_ts, job_name) values (runids.nextval, sysdate,'EMAIL_JOB')
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20; BYMINUTE=0,15,30,45; EXCLUDE=SCH_Holidays;',
enabled => TRUE,
comments => ' EMAIL ON WEEKDAYS(MO TU WE TH FR) EXCEPT SCH_Holidays every 15min from 2:00-20:00'
);
END;
/
on 11/7/2011, the Job EMAIL ran at 2AM and then subsequent repeats didn't run. As per the schedules, th job is not expected to run not even once.
I created more jobs, based on the above schedules, also all of them ran atleast once on the declared holiday. Pl. advice.