SQL> select client_name,task_name, status from dba_autotask_task where client_name = 'auto optimizer stats collection';
CLIENT_NAME TASK_NAME STATUS
---------------------------------------------------------------- ---------------------------------------------------------------- --------
auto optimizer stats collection gather_stats_prog ENABLED
SQL> col program_action for a80
SQL> col enabled for a20
SQL> select program_action, number_of_arguments, enabled from dba_scheduler_programs where owner = 'SYS' and program_name = 'GATHER_STATS_PROG';
PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABLED
-------------------------------------------------------------------------------- ------------------- --------------------
dbms_stats.gather_database_stats_job_proc 0 TRUE
--禁用统计信息自动收集任务
SQL> exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status from dba_autotask_client where client_name = 'auto optimizer stats collection';
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
--启用统计信息自动收集任务
SQL> exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status from dba_autotask_client where client_name = 'auto optimizer stats collection';
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
--禁用单个时间窗口
SQL> exec dbms_auto_task_admin.disable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => 'MONDAY_WINDOW');
PL/SQL procedure successfully completed.
SQL> col window_next_time for a50
SQL> col window_active for a50
SQL> col optimizer_stats for a20
SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients where window_name = 'MONDAY_WINDOW';
WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE OPTIMIZER_STATS
------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------
MONDAY_WINDOW 14-DEC-20 10.00.00.000000 PM PRC FALSE DISABLED
--启用单个时间窗口
SQL> exec dbms_auto_task_admin.enable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => 'MONDAY_WINDOW');
PL/SQL procedure successfully completed.
SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients where window_name = 'MONDAY_WINDOW';
WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE OPTIMIZER_STATS
------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------
MONDAY_WINDOW 14-DEC-20 10.00.00.000000 PM PRC FALSE ENABLED
--禁用所有时间窗口
SQL> exec dbms_auto_task_admin.disable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE OPTIMIZER_STATS
------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------
MONDAY_WINDOW 14-DEC-20 10.00.00.000000 PM PRC FALSE DISABLED
TUESDAY_WINDOW 15-DEC-20 10.00.00.000000 PM PRC FALSE DISABLED
WEDNESDAY_WINDOW 16-DEC-20 10.00.00.000000 PM PRC FALSE DISABLED
THURSDAY_WINDOW 10-DEC-20 10.00.00.000000 PM PRC FALSE DISABLED
FRIDAY_WINDOW 11-DEC-20 11.00.00.000000 PM PRC FALSE DISABLED
SATURDAY_WINDOW 12-DEC-20 06.00.00.000000 AM PRC FALSE DISABLED
SUNDAY_WINDOW 13-DEC-20 06.00.00.000000 AM PRC FALSE DISABLED
7 rows selected.
--启用所有时间窗口
SQL> exec dbms_auto_task_admin.enable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE OPTIMIZER_STATS
------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------
MONDAY_WINDOW 14-DEC-20 10.00.00.000000 PM PRC FALSE ENABLED
TUESDAY_WINDOW 15-DEC-20 10.00.00.000000 PM PRC FALSE ENABLED
WEDNESDAY_WINDOW 16-DEC-20 10.00.00.000000 PM PRC FALSE ENABLED
THURSDAY_WINDOW 10-DEC-20 10.00.00.000000 PM PRC FALSE ENABLED
FRIDAY_WINDOW 11-DEC-20 11.00.00.000000 PM PRC FALSE ENABLED
SATURDAY_WINDOW 12-DEC-20 06.00.00.000000 AM PRC FALSE ENABLED
SUNDAY_WINDOW 13-DEC-20 06.00.00.000000 AM PRC FALSE ENABLED
7 rows selected.
--修改时间窗口
SQL> set line 200
SQL> col repeat_interval for a60
SQL> col duration for a50
SQL> col enabled for a20
SQL> select w.window_name,
w.repeat_interval,
w.duration,
w.enabled
from dba_autotask_window_clients c, dba_scheduler_windows w
where c.window_name = w.window_name and c.optimizer_stats = 'ENABLED';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
------------------------------ ------------------------------------------------------------ -------------------------------------------------- --------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
7 rows selected.
SQL> exec dbms_scheduler.disable (NAME => '"SYS"."FRIDAY_WINDOW"', FORCE => TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.set_attribute (name => '"SYS"."FRIDAY_WINDOW"',attribute => 'repeat_interval',value=> 'freq=weekly;byday=fri;byhour=23;byminute=0;bysecond=0');
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.enable (name => '"SYS"."FRIDAY_WINDOW"');
PL/SQL procedure successfully completed.
SQL> select w.window_name,
w.repeat_interval,
w.duration,
w.enabled
from dba_autotask_window_clients c, dba_scheduler_windows w
where c.window_name = w.window_name and c.optimizer_stats = 'ENABLED';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
------------------------------ ------------------------------------------------------------ -------------------------------------------------- --------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW freq=weekly;byday=fri;byhour=23;byminute=0;bysecond=0 +000 04:00:00 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
7 rows selected.