Update maintenance window duration in oracle database

Update maintenance window duration in oracle database-

Scenario- Current issue is we have default database level gather stats scheduled and maintenance window is 4 hrs, but maintenance task is not able to complete in he scheduled window and status showing STOPPED, here we can update the maintenance window to 6 0r 8 hr to complete the task as per task duration. Below is the complete steps how to update maintenance window to 6 hr, you can increase the window same way as per your requirement.

1-Connect to the to database

2-check the current setting for maintenance windows with below command

col REPEAT_INTERVAL for a80
select window_name, repeat_interval, duration from dba_scheduler_windows order by window_name;

Sample Output-
WINDOW_NAME       REPEAT_INTERVAL                                          DURATION
 
 FRIDAY_WINDOW     freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0    +000 06:00:00
 MONDAY_WINDOW     freq=daily;byday=MON;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
 THURSDAY_WINDOW   freq=daily;byday=THU;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
 WEEKEND_WINDOW    freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0      +002 00:00:00

3-execute below script to change WINDOW DURATION to 6 hrs for weekdays WINDOW only (MONDAY to FRIDAY)

BEGIN
   dbms_scheduler.disable(
     name  => 'MONDAY_WINDOW');
   dbms_scheduler.set_attribute(
     name      => 'MONDAY_WINDOW',
     attribute => 'DURATION',
     value     => numtodsinterval(6, 'hour'));
   dbms_scheduler.enable(
     name => 'MONDAY_WINDOW');
 END;
 /
 BEGIN
   dbms_scheduler.disable(
     name  => 'TUESDAY_WINDOW');
   dbms_scheduler.set_attribute(
     name      => 'TUESDAY_WINDOW',
     attribute => 'DURATION',
     value     => numtodsinterval(6, 'hour'));
   dbms_scheduler.enable(
     name => 'TUESDAY_WINDOW');
 END;
 /
 BEGIN
   dbms_scheduler.disable(
     name  => 'WEDNESDAY_WINDOW');
   dbms_scheduler.set_attribute(
     name      => 'WEDNESDAY_WINDOW',
     attribute => 'DURATION',
     value     => numtodsinterval(6, 'hour'));
   dbms_scheduler.enable(
     name => 'WEDNESDAY_WINDOW');
 END;
 /
 BEGIN
   dbms_scheduler.disable(
     name  => 'THURSDAY_WINDOW');
   dbms_scheduler.set_attribute(
     name      => 'THURSDAY_WINDOW',
     attribute => 'DURATION',
     value     => numtodsinterval(6, 'hour'));
   dbms_scheduler.enable(
     name => 'THURSDAY_WINDOW');
 END;
 /
 BEGIN
   dbms_scheduler.disable(
     name  => 'FRIDAY_WINDOW');
   dbms_scheduler.set_attribute(
     name      => 'FRIDAY_WINDOW',
     attribute => 'DURATION',
     value     => numtodsinterval(6, 'hour'));
   dbms_scheduler.enable(
     name => 'FRIDAY_WINDOW');
 END;
 /

PL/SQL procedure successfully completed.

4-Validate the status and duration

col REPEAT_INTERVAL for a80
col WINDOW_NAME for a40
col DURATION for a30
select window_name, repeat_interval, duration from dba_scheduler_windows order by window_name;