Changing Oracle Scheduler Maintenance Windows Timing and Settings

Since the advent of Oracle Scheduler, there have been maintenance windows defined with default setting to run some automatic maintenance jobs like auto gather stats job or auto space advisor job etc. There are scenarios whereby DBAs need to change the maintenance windows setting if maintenance windows span peak hours. By default, maintenance jobs run during nights and weekends as these are the off-peak hours in most of the case. However, this may not be the case for every production database. In this article I would explain how to simply change any maintenance window start time and/or duration. For12c and above, this setting is individually defined in root container and PDBs.

As you can see my database has daily maintenance windows start time set to 10 PM and duration is 4 hours. Weekend (Saturday and Sunday) maintenance windows start time is 6 AM and duration is 20 hours. 

col duration for a15 col start_date for a40 col window_name for a17 col LAST_START_DATE for a40 col NEXT_START_DATE for a40 select window_name,last_start_date,duration,REPEAT_INTERVAL from DBA_SCHEDULER_WINDOWS;   WINDOW_NAME       LAST_START_DATE                          DURATION        REPEAT_INTERVAL ----------------- ---------------------------------------- --------------- ---------------------------------------- MONDAY_WINDOW     28-NOV-22 10.00.00.164472 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0   TUESDAY_WINDOW    29-NOV-22 10.00.00.054724 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0 ;bysecond=0   WEDNESDAY_WINDOW  30-NOV-22 10.00.00.009479 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0   THURSDAY_WINDOW   01-DEC-22 10.00.00.151630 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0   FRIDAY_WINDOW     25-NOV-22 10.00.00.096499 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0   SATURDAY_WINDOW   26-NOV-22 06.00.00.096703 AM ETC/UTC     +000 20:00:00   freq=daily;byday=SAT;byhour=10;byminute=0;bysecond=0   SUNDAY_WINDOW     27-NOV-22 06.00.00.166465 AM ETC/UTC     +000 20:00:00   freq=daily;byday=SAT;byhour=10;byminute=0;bysecond=0

If I want to set the start time of daily maintenance windows to 2 AM and weekend windows start time to 10 AM, I will execute following set of statements.

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=10;byminute=0;bysecond=0'); execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=10;byminute=0;bysecond=0'); execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=2;byminute=0;bysecond=0'); execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=2;byminute=0;bysecond=0'); execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=2;byminute=0;bysecond=0'); execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=2;byminute=0;bysecond=0'); execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=2;byminute=0;bysecond=0');

After this, the following query will show the next start date/time of each of these maintenance windows.

select window_name,next_start_date,duration,REPEAT_INTERVAL from DBA_SCHEDULER_WINDOWS;   WINDOW_NAME       NEXT_START_DATE                          DURATION        REPEAT_INTERVAL ----------------- ---------------------------------------- --------------- ---------------------------------------- MONDAY_WINDOW     05-DEC-22 02.00.00.000000 AM ETC/UTC     +000 04:00:00   freq=daily;byday=MON;byhour=2;byminute=0;bysecond=0   TUESDAY_WINDOW    06-DEC-22 02.00.00.000000 AM ETC/UTC     +000 04:00:00   freq=daily;byday=TUE;byhour=2;byminute=0 ;bysecond=0   WEDNESDAY_WINDOW  07-DEC-22 02.00.00.000000 AM ETC/UTC     +000 04:00:00   freq=daily;byday=WED;byhour=2;byminute=0;bysecond=0   THURSDAY_WINDOW   08-DEC-22 02.00.00.000000 AM ETC/UTC     +000 04:00:00   freq=daily;byday=THU;byhour=2;byminute=0 ;bysecond=0   FRIDAY_WINDOW     09-DEC-22 02.00.00.000000 AM ETC/UTC     +000 04:00:00   freq=daily;byday=FRI;byhour=2;byminute=0;bysecond=0   SATURDAY_WINDOW   03-DEC-22 10.00.00.000000 AM ETC/UTC     +000 20:00:00   freq=weekly;byday=SAT;byhour=10;byminute=0;bysecond=0   SUNDAY_WINDOW     04-DEC-22 10.00.00.000000 AM ETC/UTC     +000 20:00:00   freq=daily;byday=SUN;byhour=10;byminute=0;bysecond=0

I have only changed start time of the maintenance windows here, but if you also want to change the duration of any window, you may set the duration attribute for the window. For example, in order to change run duration of SATURDAY_WINDOW to 9 hours, execute following procedure.

exec DBMS_SCHEDULER.set_attribute( name => 'SYS.SATURDAY_WINDOW',  attribute => 'DURATION',  value => numtodsinterval(540, 'minute'));