How to change Scheduler maintenance windows? ..
from web https://connor-mcdonald.com/2020/08/07/modifying-scheduler-windows/
set linesize 500 pagesize 400
col WINDOW_NAME for a25
col REPEAT_INTERVAL for a70
col DURATION for a25
col SCHEDULE_OWNER for a14
select SCHEDULE_OWNER ,window_name, repeat_interval, duration from dba_scheduler_windows
--where 1=1
order by WINDOW_NAME
;
SCHEDULE_OWNER 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 *
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 *
9 rows selected.
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
for i in 1 .. x.count
loop
DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/
output !!!
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
SQL> SQL> 2 3 4 5 for i in 1 .. x.count
6
7 loop
8 DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
9
10 DBMS_SCHEDULER.set_attribute(
11 name => 'SYS.'||x(i)||'_WINDOW',
12 attribute => 'REPEAT_INTERVAL',
13 value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/ 14 15 16 17 18 19 20 21 22 23
PL/SQL procedure successfully completed.
======================================================================
from
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
to
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW FREQ=WEEKLY;BYDAY=SAT;BYHOUR=03;BYMINUTE=0;BYSECOND=0 +000 01:00:00