How to change Scheduler maintenance windows in Oracle Step by Step

Pre-implementation


---To check job history

ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS'; 

column job_name format a30 
column log_date format a40 
column actual_start_date format a40 
column run_duration format a60 
column cpu_used format a50  
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where job_name like '%STA%' and trunc(log_date) = trunc(sysdate -2) ORDER BY LOG_DATE DESC; 

----stats job window--------

select job_name,PROGRAM_NAME,SCHEDULE_NAME,SCHEDULE_TYPE,START_DATE,STATE   from dba_scheduler_jobs where job_name like '%STA%'; 

--To check job window

set linesize 120
column WINDOW_NAME format a20
column ENABLED format a7
column REPEAT_INTERVAL format a60
column DURATION format a15

select job_name,job_type,program_name,schedule_name,job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

-- To check job window details

SQL>select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in (select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='EVERYNIGHT_WINDOW_GROUP')
/
                      
SQL> select * from dba_scheduler_wingroup_members where window_group_name = 'EVERYNIGHT_WINDOW_GROUP';

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
EVERYNIGHT_WINDOW_GROUP        EVERYNIGHT_WINDOW


select window_name,repeat_interval,duration from dba_scheduler_windows where window_name='EVERYNIGHT_WINDOW';


Implementation
--------------------------

Plan A : To change duration of existing window

BEGIN
dbms_scheduler.disable(
    name  => 'EVERYNIGHT_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'EVERYNIGHT_WINDOW',
    attribute => 'DURATION',
    value     => numtodsinterval(6, 'hour'));
dbms_scheduler.enable(
    name => 'EVERYNIGHT_WINDOW');
END;

 exec dbms_scheduler.set_attribute('EVERYNIGHT_WINDOW','repeat_interval','FREQ=daily;byhour=01;byminute=0; bysecond=0');

Plan B: create new window and assigned to gather stat job


BEGIN
dbms_scheduler.create_window(
    window_name     => 'EVERYNIGHT_WINDOW_6HRS',
    duration        =>  numtodsinterval(6, 'hour'),
    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
    repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_window_group_member(
    group_name  => 'EVERYNIGHT_WINDOW_GROUP_6HRS',
    window_list => 'EVERYNIGHT_WINDOW_6HRS');
END;


exec dbms_scheduler.set_attribute('EVERYNIGHT_WINDOW_6HRS','repeat_interval','FREQ=daily;byhour=01;byminute=0; bysecond=0');

 
Post Implementation
------------------ ---------------
SQL> column REPEAT_INTERVAL format a60
column DURATION format a15
SQL> SQL>
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows;


Verification
---------------

To check job window

set linesize 120
column WINDOW_NAME format a20
column ENABLED format a7
column REPEAT_INTERVAL format a60
column DURATION format a15

select window_name, enabled, repeat_interval, duration from dba_scheduler_windows;