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;