Search This Blog

Total Pageviews

Sunday, 18 February 2024

How to change Scheduler maintenance windows ?


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
               

Oracle DBA

anuj blog Archive