Change your MAINTENANCE_PLAN schedules via SQLPlus


Just sharing my way of changing MAINTENANCE_WINDOW defined by default in your Oracle Database via SQL Plus.
They may not fit into your workload as default defined.

To re-set my config i only need to run :

SQL> @NOMT1_settings.sql

This script “NOMT1_settings.sql” is a Database custom settings script and is adjusted per degree of parallelism and DURATION / schedule .

-- -----------------------------------------------------------
prompt "CUSTOM DB SETTINGS :"
-- -----------------------------------------------------------
-- Set DEGREE level
prompt "Executing ... DBMS_STATS.set_global_prefs('DEGREE','4')"
exec DBMS_STATS.set_global_prefs('DEGREE','4');
prompt "Query the settings for weeknights now."
set lines 2000
column REPEAT_INTERVAL format a100
select window_name, repeat_interval, duration from dba_scheduler_windows where WINDOW_NAME='WEEKNIGHT_WINDOW';
prompt "re-Defining the WEEKNIGHT_WINDOW"
EXEC DBMS_SCHEDULER.disable(name => 'SYS.WEEKNIGHT_WINDOW', force => TRUE);
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.WEEKNIGHT_WINDOW',attribute => 'DURATION',value => numtodsinterval(60, 'minute'));
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.WEEKNIGHT_WINDOW',attribute => 'REPEAT_INTERVAL',value => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=18;BYMINUTE=0;BYSECOND=0');
EXEC DBMS_SCHEDULER.enable(name=>'SYS.WEEKNIGHT_WINDOW');
prompt "Check the changes made."
select window_name, repeat_interval, duration from dba_scheduler_windows where WINDOW_NAME='WEEKNIGHT_WINDOW';
prompt "Execute the Global Window settings."
@@global_settings

Above script calls “global_settings.sql” which is the same for all other custom database scripts and is displayed below.


-- -----------------------------------------------------------
prompt "GLOBAL SETTINGS MAINTENANCE PLAN:"
-- -----------------------------------------------------------
prompt "Disable Scheduler or Maintenance before changing anything."
EXEC DBMS_SCHEDULER.disable(name => 'SYS.MAINTENANCE_WINDOW_GROUP',force => TRUE);
prompt "Assign all Windows to default Maintenance Window Group."
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"WEEKNIGHT_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"MONDAY_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"TUESDAY_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"WEDNESDAY_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"THURSDAY_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"FRIDAY_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"SATURDAY_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"SUNDAY_WINDOW"');
EXEC DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"WEEKEND_WINDOW"');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.MONDAY_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.TUESDAY_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.WEDNESDAY_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.THURSDAY_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.FRIDAY_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.SATURDAY_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.SUNDAY_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.WEEKNIGHT_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
EXEC DBMS_SCHEDULER.set_attribute(name => 'SYS.WEEKEND_WINDOW',attribute => 'RESOURCE_PLAN',value => 'DEFAULT_MAINTENANCE_PLAN');
prompt "Enable the Global Automated Maintenance Window."
EXEC DBMS_AUTO_TASK_ADMIN.enable;
exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
exec dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
prompt "Disable all Windows."
prompt "auto space advisor"
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'MONDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'TUESDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'WEDNESDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'FRIDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'SATURDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'SUNDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'WEEKEND_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => 'WEEKNIGHT_WINDOW');
prompt "sql tuning advisor"
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'MONDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'TUESDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'WEDNESDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'FRIDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'SATURDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'SUNDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'WEEKEND_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'WEEKNIGHT_WINDOW');
prompt "auto optimizer stats collection"
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'MONDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'TUESDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'WEDNESDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'THURSDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'FRIDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'SATURDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'SUNDAY_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'WEEKEND_WINDOW');
EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'WEEKNIGHT_WINDOW');
prompt "Enable Windows."
prompt "auto optimizer stats collection"
EXEC DBMS_AUTO_TASK_ADMIN.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'WEEKNIGHT_WINDOW');
prompt "Disable all windows except WEEKNIGHT and WEEKEND"
EXEC DBMS_SCHEDULER.disable(name=>'SYS.MONDAY_WINDOW');
EXEC DBMS_SCHEDULER.disable(name=>'SYS.TUESDAY_WINDOW');
EXEC DBMS_SCHEDULER.disable(name=>'SYS.WEDNESDAY_WINDOW');
EXEC DBMS_SCHEDULER.disable(name=>'SYS.THURSDAY_WINDOW');
EXEC DBMS_SCHEDULER.disable(name=>'SYS.FRIDAY_WINDOW');
EXEC DBMS_SCHEDULER.disable(name=>'SYS.SATURDAY_WINDOW');
EXEC DBMS_SCHEDULER.disable(name=>'SYS.SUNDAY_WINDOW');
EXEC DBMS_SCHEDULER.enable(name=>'SYS.WEEKNIGHT_WINDOW');
EXEC DBMS_SCHEDULER.enable(name=>'SYS.WEEKEND_WINDOW');
prompt "Enable Maintenance Window Group."
EXEC DBMS_SCHEDULER.enable(name => 'SYS.MAINTENANCE_WINDOW_GROUP');