Oracle修改统计信息运行窗口及窗口期

---

提示：以下是本篇文章正文内容，下面案例可供参考
工作中如果遇到需要修改Oracle统计信息收集任务时间，可参考以下命令进行修改：

一、启用/关闭统计任务

禁用收集：

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

启用收集：

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

查询任务状态：
select client_name,status from dba_autotask_client;

二、修改任务运行窗口及窗口期

调整任务执行窗口时间:
--周一:9点执行，运行窗口4小时

begin

  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=9;byminute=0;bysecond=0');

  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

--周二:9点执行，运行窗口4小时

begin

  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=9;byminute=0;bysecond=0');

  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

--周三:9点执行，运行窗口4小时

begin

  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=9;byminute=0;bysecond=0');

  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

--周四:9点执行，运行窗口4小时

begin

  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=9;byminute=0;bysecond=0');

  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

--周五:9点执行，运行窗口4小时

begin

  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=9;byminute=0;bysecond=0');

  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

--周六:9点执行，运行窗口8小时

begin

  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=9;byminute=0;bysecond=0');

  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 8:00:00');

end;

/

--周日:9点执行，运行窗口8小时

begin

  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=9;byminute=0;bysecond=0');

  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 8:00:00');

end;

/


查询修改后的窗口期：
select t1.window_name,t1.repeat_interval,t1.duration,enabled
from dba_sscheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED')