How to change Scheduler maintenance windows

There are a few possible reasons why you might be one of those people why you might be using your database late in the evening. These include

• you support a 24 by 7 system and so you often need to be online late at night,
• you are a hopeless geek and love tinkering with the database when you should be in bed,
• you now live in a virtual world and thus many of your meetings or seminars or presentations are being done in a unfriendly time zone

I tick some of these boxes and thus I am often using the database on my own home machine late in the evening. It is always frustrating when the clock ticks over to 10:00 PM and suddenly the fan on my machine goes nuts, the lights go dim in my house and I can tell that the many many databases on my machine have suddenly decided it is time to get busy.

The reason for this is that the database has a number of tasks that get run at regular intervals in order to keep your database in tiptop condition. For example, the gathering of optimiser statistics and running the various advisors that come by default with your installation. Out of the box these tasks assume you are running a typical business day style of operation for your database, thus 10:00 PM is the default kick-off time for these tasks, and they’ll run for up to 4 hours.

Let’s take a look at the default scheduler windows that come in most recent versions of the Oracle database

SQL> select window_name, repeat_interval, duration
  2  from dba_scheduler_windows;

WINDOW_NAME              REPEAT_INTERVAL                                                          DURATION
------------------------ ------------------------------------------------------------------------ ---------------
MONDAY_WINDOW            freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
TUESDAY_WINDOW           freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
WEDNESDAY_WINDOW         freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
THURSDAY_WINDOW          freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
FRIDAY_WINDOW            freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
SATURDAY_WINDOW          freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                     +000 20:00:00
SUNDAY_WINDOW            freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                     +000 20:00:00
WEEKEND_WINDOW           freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                      +002 00:00:00
WEEKNIGHT_WINDOW         freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0    +000 08:00:00

If 10:00 PM is not a good time then it is easily to change these windows. I generally recommend people not to disable the windows because it is easy to get into a position where the nightly tasks you should be running are forgotten and never run again. Rest assured when you log a support call at some stage in the future and your database has not run any of these regular tasks for the past year, that is going to make the support technician’s job that much harder to help you, because you have diverted so far from default operations of the database.

If you need to change the windows I would suggest that you simply move the start time, and similarly you can shrink the duration to a smaller time scale if needed. For me, I run under the assumption that I will not be using the database at 2:00 AM (or that if I am I should not be and the busy machine will hopefully force me to go to bed!)

Here is a simple anonymous block that loops through the weekday windows to move the start time and duration:

SQL> declare
  2    x sys.odcivarchar2list :=
  3          sys.odcivarchar2list('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY');
  4  BEGIN
  5  for i in 1 .. x.count
  6  loop
  7    DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
  8
  9    DBMS_SCHEDULER.set_attribute(
 10      name      => 'SYS.'||x(i)||'_WINDOW',
 11      attribute => 'REPEAT_INTERVAL',
 12      value     => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=02;BYMINUTE=0;BYSECOND=0');
 13
 14    DBMS_SCHEDULER.set_attribute(
 15      name      => 'SYS.'||x(i)||'_WINDOW',
 16      attribute => 'DURATION',
 17      value     =>  numtodsinterval(60, 'minute'));
 18
 19    DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
 20  end loop;
 21  END;
 22  /

PL/SQL procedure successfully completed.

Remember that in a multi-tenant environment, these windows may be present in each of your pluggable as well as the root.

TL;DR: You can change the scheduler windows to a time that best suits your requirements but please leave them in place so that important database tasks do not get overlooked