Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle dbms_scheduler for weekday execution

Oracle Database Tips by Donald Burleson

For more working details on Oracle job scheduling and a complete code depot, see the wonderful $16.95 book Oracle Job Scheduling by Dr. Timothy Hall.  You can get the best deal (30%-off by) buying it directly from the publisher.

Also see Oracle dbms_scheduler examples and:

Oracle cron crontab examples

Windows Oracle Job Schedulers examples

It can be very tricky scheduling Oracle jobs that run only on weekdays.  Of course, it's much easier to use the 10g dbms_scheduler:

'freq=hourly; byday=MON,TUE,WED,THU,FRI; byminute=0; bysecond=0;'

Consider this example of a job to run hourly, but only on weekdays:

- Submit job to begin at 0600 and run every hour, Monday - Friday
      next_day(SYSDATE - 1,''MONDAY''),
      next_day(SYSDATE - 1,''TUESDAY''),
      next_day(SYSDATE - 1,''WEDNESDAY''),
      next_day(SYSDATE - 1,''THURSDAY''),
      next_day(SYSDATE - 1,''FRIDAY'')



The following commentary by Mike Ault shows the perils of complex job scheduling for weekdays. 

I submit it today, it starts the job tomorrow (Monday) at 6am…ok, next iteration:

SQL> select to_char(trunc(

2 least(

3 next_day(to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'MONDAY'),

4 next_day(to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'TUESDAY'),

5 next_day(to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'WEDNESDAY'),

6 next_day(to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'THURSDAY'),

7 next_day(to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'FRIDAY')

8 )

9* +1/24,'HH'),'dd-mon-yyyy hh24:mi') from dual

SQL> /

Ok, 15-may-2006 07:00

So how about next day?

SQL> select to_char(trunc(

2 least(

3 next_day(to_date('16-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'MONDAY'),

4 next_day(to_date('16-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'TUESDAY'),

5 next_day(to_date('16-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'WEDNESDAY'),

6 next_day(to_date('16-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'THURSDAY'),

7 next_day(to_date('16-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'FRIDAY')

8 )

9* +1/24,'HH'),'dd-mon-yyyy hh24:mi') from dual

SQL> /

16-may-2006 07:00

OK…now last thing…Fridays

SQL> select to_char(trunc(

2 least(

3 next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'MONDAY'),

4 next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'TUESDAY'),

5 next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'WEDNESDAY'),

6 next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'THURSDAY'),

7 next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'FRIDAY')

8 )

9* +1/24,'HH'),'dd-mon-yyyy hh24:mi') from dual

SQL> /

20-may-2006 00:00

Hmm…maybe ok…what happens after Midnight?

SQL> select to_char(trunc(

2 least(

3 next_day(to_date('20-may-2006 00:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'MONDAY'),

4 next_day(to_date('20-may-2006 00:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'TUESDAY'),

5 next_day(to_date('20-may-2006 00:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'WEDNESDAY'),

6 next_day(to_date('20-may-2006 00:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'THURSDAY'),

7 next_day(to_date('20-may-2006 00:00:01','dd-mon-yyyy hh24:mi:ss') - 1,'FRIDAY')

8 )

9* +1/24,'HH'),'dd-mon-yyyy hh24:mi') from dual

SQL> /

22-may-2006 01:00

Well…not exactly 6am….but at least it hits Monday.

Jon Emmons also notes:

Hmm, interesting.  The erroneous result Mike got on this SQL is because he ran this on a day that is excluded.  This would only cause a problem at initial startup, and then only if the first run falls on  a weekend.

> SQL> select to_char(trunc(
>  2  least(
>  3  next_day(SYSDATE - 1,'MONDAY'),
>  4  next_day(SYSDATE - 1,'TUESDAY'),
>  5  next_day(SYSDATE - 1,'WEDNESDAY'),
>  6  next_day(SYSDATE - 1,'THURSDAY'),
>  7  next_day(SYSDATE - 1,'FRIDAY')
>  8  )
>  9* +1/24,'HH'),'dd-mon-yyyy hh24:mi') from dual
> SQL> /
> -----------------
> 15-may-2006 12:00

If we can overlook that, I think we can manipulate the INTERVAL into
doing what we want by subtracting 23 hours instead of 24.  If we then
take the least of the NEXT_DAY, M-F after that and trunc it we get the
additional advantage of not having to bump it up the hour (since we
subtracted 23 then moved forward 1 (or more) days.

select to_char(trunc(
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
next_day(to_date('19-may-2006 23:00:01','dd-mon-yyyy hh24:mi:ss') -
),'HH'),'dd-mon-yyyy hh24:mi') from dual

22-may-2006 00:00

That at least keeps the example from running at midnight on Saturday.  I did not gather that the intention of this job was to start every Monday at 6:00am.  I thought it was just supposed to start at 6:00am tomorrow and continue at 1 hour intervals, M-F.

This is one of those examples which would be more easily scheduled with UNIX cron, but I think could be done here.  One (messy) way would be to have another job disable this job after 11:00pm on Friday and another enable it at 5:05am on Monday.  3 jobs to do the work of 1 isn't very pretty.

Let me know if this makes sense.  Making the job start at 6:00 am on Monday would probably take a fairly long INTERVAL definition and seems like a fairly unlikely scenario.

    next_day(SYSDATE - 23/24,''MONDAY''),
    next_day(SYSDATE - 23/24,''TUESDAY''),
    next_day(SYSDATE - 23/24,''WEDNESDAY''),
    next_day(SYSDATE - 23/24,''THURSDAY''),
    next_day(SYSDATE - 23/24,''FRIDAY'')




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.