3

I have an Oracle DBMS_SCHEDULER job that normally runs every hour on the hour. No problem. But, I recently needed to make an exception to this, so that that last run every Saturday (i.e., the one at 11pm) would run a little earlier. (Reason is a long story that involves company politics.)

I thought I could do this easily using an EXCLUDE clause, but I could not get that to work. I was getting errors like:

ORA-27422: usage of BYHOUR not supported in embedded calendar application...
ORA-06512: at "SYS.DBMS_ISCHED", line 135
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at "XDH.ROBOT", line 680
ORA-06512: at line 1

The only way I found to get it to work was to create three(!) schedules as follows:

dbms_scheduler.create_schedule(
    schedule_name   => 'sunday_to_friday',
    repeat_interval => 'freq = hourly; interval = 1; byday=sun,mon,tue,wed,thu,fri' );

dbms_scheduler.create_schedule( --All but the last run on Saturday.
    schedule_name   => 'saturday_most',
    repeat_interval => 
        'freq = hourly; byday=sat;'||
        ' byhour=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22' );

dbms_scheduler.create_schedule( --Last run on Saturday.
    schedule_name   => 'saturday_last',
    repeat_interval => 
        'freq = weekly; byday=sat; byhour=22; byminute=54 ' );

And then I used 'sunday_to_friday, saturday_most, saturday_last' as the repeat_interval for the job itself.

This seems clumsy. Is there a better/simpler way to do this kind of thing?

We are currently using Oracle 11g Release 11.2.0.4.0.

lcarl
  • 31
  • 2
  • Actually, if this works, I don't think its clumsy, but I think the business rule is wacko though ;) – tbone Jan 19 '17 at 00:03
  • The rule is wacko, I agree. The people who manage the servers have decided to shut them down every Saturday at 11 for their own reasons and I decided not to argue as my users don't work at that time. – lcarl Jan 20 '17 at 14:55
  • Ahh, so you mean they shutdown the Oracle servers? If thats the case, I believe the scheduled jobs will fire immediately after instance startup (unless, perhaps, the job is defined as detached). So all this may be a moot point, your hourly job should fire at 11:05 or whenever the instance is back up. – tbone Jan 20 '17 at 16:48
  • The job usually runs very quickly, and 3 out of 4 times it has completed before the shutdown, but sometimes it is interrupted by the shutdown. For several reasons, e.g, my OCD, I'd like it to finish. – lcarl Jan 20 '17 at 17:02

0 Answers0