Automated Maintence Task

확인

SELECT * FROM DBA_AUTOTASK_CLIENT;
SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT;
/*
CLIENT_NAME	STATUS
sql tuning advisor	ENABLED
auto optimizer stats collection	ENABLED
auto space advisor	ENABLED
*/

실행 주기 확인

SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
/*
WINDOW_NAME	WINDOW_NEXT_TIME	WINDOW_ACTIVE	AUTOTASK_STATUS	OPTIMIZER_STATS	SEGMENT_ADVISOR	SQL_TUNE_ADVISOR
MONDAY_WINDOW	2022/12/19 22:00:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED
TUESDAY_WINDOW	2022/12/13 22:00:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED
WEDNESDAY_WINDOW	2022/12/14 22:00:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED
THURSDAY_WINDOW	2022/12/15 22:00:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED
FRIDAY_WINDOW	2022/12/16 22:00:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED
SATURDAY_WINDOW	2022/12/17 06:00:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED
SUNDAY_WINDOW	2022/12/18 06:00:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED
*/

작업 시간 확인, 변경

--확인
SELECT * FROM DBA_SCHEDULER_WINDOWS;
SELECT WINDOW_NAME, REPEAT_INTERVAL, DURATION FROM DBA_SCHEDULER_WINDOWS;
/*
WINDOW_NAME	REPEAT_INTERVAL
MONDAY_WINDOW	freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000
TUESDAY_WINDOW	freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000
WEDNESDAY_WINDOW	freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000
THURSDAY_WINDOW	freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000
FRIDAY_WINDOW	freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000
SATURDAY_WINDOW	freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0	+00 20:00:00.000000
SUNDAY_WINDOW	freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0	+00 20:00:00.000000
WEEKNIGHT_WINDOW	freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0	+00 08:00:00.000000
WEEKEND_WINDOW	freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0	+02 00:00:00.000000
*/

--변경
BEGIN
    DBMS_SCHEDULER.DISABLE(NAME => 'MONDAY_WINDOW');
    DBMS_SCHEDULER.SET_ATTRIBUTE(NAME => 'MONDAY_WINDOW', ATTRIBUTE => 'DURATION', VALUE => NUMTODSINTERVAL(6, 'HOUR'));
    DBMS_SCHEDULER.ENABLE(NAME => 'MONDAY_WINDOW');
END;
/

해당 시간내에 끝나지 않으면 작업 취소 후 Alterlog에 ORA-01013이 기록됨

태스크 ON, OFF

--전 태스크 OFF
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE;

--sql tuning advisor OFF
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor', OPERATION => NULL, WINDOW_NAME => NULL);

--auto space advisor OFF
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor', OPERATION => NULL, WINDOW_NAME => NULL);

--auto optimizer stats collection OFF
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection', OPERATION => NULL, WINDOW_NAME => NULL);

--전 태스크 ON
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE;

--sql tuning advisor ON
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'sql tuning advisor', OPERATION => NULL, WINDOW_NAME => NULL);

--auto space advisor ON
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto space advisor', OPERATION => NULL, WINDOW_NAME => NULL);

--auto optimizer stats collection ON
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto optimizer stats collection', OPERATION => NULL, WINDOW_NAME => NULL);

--auto optimizer stats collection ON (일요일에만)
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto optimizer stats collection', OPERATION => NULL, WINDOW_NAME => 'SUNDAY_WINDOW');