SELECT CLIENT_NAME,STATUS FROM DBA_autotask_client; SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'auto optimizer stats collection'; DBMS_AUTO_TASK_ADMIN.disable (CLIENT_NAME => 'AUTO OPTIMIZER STATS' collection', OPERATION => NULL, WINDOW_NAME => NULL); END; SQL Tuning Advisor(STA) BEGIN DBMS_AUTO_TASK_ADMIN.disable (CLIENT_NAME => 'SQL Tuning Advisor ', OPERATION => NULL, WINDOW_NAME => NULL); END; DISABLE(CLIENT_NAME => 'auto space advisor', DISABLE(CLIENT_NAME => 'auto space advisor', OPERATION => NULL, WINDOW_NAME => NULL); END; DBMS_AUTO_TASK_ADMIN.ENABLED (CLIENT_NAME => 'AUTO OPTIMIZER STATS COLLECTION BEGIN DBMS_AUTO_TASK_ADMIN.ENABLED (CLIENT_NAME =>' AUTO OPTIMIZER STATS COLLECTION BEGIN collection', OPERATION => NULL, WINDOW_NAME => NULL); END; SQL Tuning Advisor(STA) BEGIN DBMS_AUTO_TASK_ADMIN.enable (CLIENT_NAME => 'SQL Tuning Advisor ', ENABLE(CLIENT_NAME =>' SQL Tuning Advisor ', ENABLE(CLIENT_NAME => 'SQL Tuning Advisor') OPERATION => NULL, WINDOW_NAME => NULL); END; Auto space advisor BEGIN dbms_auto_task_admin. ENABLE(CLIENT_NAME => 'auto space advisor', OPERATION => NULL, WINDOW_NAME => NULL); END; / -- Execute dbms_auto_task_admin.enable; / -- Execute dbms_auto_task_admin.enable; select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS'; Set Lines 350; Set Lines 350; COL WINDOW_NAME FOR A20; COL DURATION FOR A15; COL REPEAT_INTERVAL FOR A70; COL NEXT_START_DATE FOR A25; COL LAST_START_DATE FOR A25; SELECT W.WINDOW_NAME, W.REPEAT_INTERVAL, W.DURATION, W.NEXT_START_DATE, W.LAST_START_DATE, W.ENABLED FROM DBA_AUTOTASK_WINDOW_CLIENTS C, DBA_SCHEDULER_WINDOWS W WHERE C.WINDOW_NAME = W.WINDOW_NAME AND C.OPTIMIZER_STATS = 'ENABLED'; COL JOB_NAME FOR A30 COL ACTUAL_START_DATE FOR A40 COL RUN_DURATION FOR A30 SET LINES 180 PAGES 100 -- 10G SELECT OWNER,  JOB_NAME, STATUS, ACTUAL_START_DATE, RUN_DURATION FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'GATHER_STATS_JOB' ORDER BY 4; -- 11G SELECT OWNER, JOB_NAME, STATUS, ACTUAL_START_DATE, RUN_DURATION FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME LIKE 'ORA$AT_OS_OPT_S%' ORDER BY 4; Dbms_scheduler. DISABLE(NAME= bb0 '"SYS"."SATURDAY_WINDOW"', FORCE=>TRUE); END; Dbms_scheduler. SET_ATTRIBUTE(NAME=>'"SYS"."SATURDAY_WINDOW"), ATTRIBUTE=>'REPEAT_INTERVAL', VALUE=>'freq=daily; byday=SAT; byhour=8; byminute=0; bysecond=0'); END; SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily '; byday=MON; byhour=22; byminute=0; bysecond=0'); EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily; byday=TUE; byhour=22; byminute=0; bysecond=0'); EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily; byday=WED; byhour=22; byminute=0; bysecond=0'); EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily; byday=THU; byhour=22; byminute=0; bysecond=0'); EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily; byday=FRI; byhour=22; byminute=0; bysecond=0'); EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily; byday=SAT; byhour=22; byminute=0; bysecond=0'); EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily; byday=SUN; byhour=6; byminute=0; bysecond=0'); BEGIN dbms_scheduler. DISABLE(NAME => 'SATURDAY_WINDOW'); DBMS_SCHEDULER.SET_ATTRIBUTE(NAME => 'SATURDAY_WINDOW', ATTRIBUTE => 'DURATION', VALUE => NUMTODSINTERVAL(8, 'hour')); DBMS_SCHEDULER.ENABLE(NAME => 'SATURDAY_WINDOW'); END; COL WINDOW_NAME FOR A20; COL DURATION FOR A15; COL REPEAT_INTERVAL FOR A70; 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; COL WINDOW_NAME FOR a18; COL DURATION FOR a15; COL JOB_DURATION FOR a15; COL REPEAT_INTERVAL FOR A60; SELECT CH.WINDOW_NAME, W.REPEAT_INTERVAL, W.DURATION JOB_DURATION, TO_CHAR(CH.WINDOW_START_TIME, 'yyyy-mm-dd hh24:mi:ss') EXEC_START_TIME, TO_CHAR(CH.WINDOW_END_TIME, 'yyyy-mm-dd hh24:mi:ss') EXEC_END_TIME, CH.JOBS_CREATED, CH.JOBS_COMPLETED FROM DBA_AUTOTASK_CLIENT_HISTORY CH, DBA_SCHEDULER_WINDOWS W WHERE CH.CLIENT_NAME LIKE '%stats%' AND CH.WINDOW_NAME = W.WINDOW_NAME AND W.ENABLED = 'TRUE' AND WINDOW_START_TIME >= SYSDATE - 7 ORDER BY WINDOW_START_TIME DESC; SELECT CLIENT_NAME, JOB_NAME, JOB_NAME, JOB_NAME JOB_SCHEDULER_STATUS FROM DBA_AUTOTASK_CLIENT_JOB WHERE CLIENT_NAME = 'auto optimizer stats collection'; Exec dbms_scheduler. DISABLE(NAME => 'FRIDAY_WINDOW'); exec dbms_scheduler.stop_job(ORA$AT_OS_OPT_SY_161); exec dbms_scheduler.stop_job(ORA$AT_OS_OPT_SY_161,force); begin DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL ); end; /