sysaux tablespace는 10g에서 새로 추가된 system default tablespace의 하나로 기존에 system tablespace에

저장되던 각종 ORACLE OPTION들의 schema가 저장되며 10g의 new feature인 AWR(auto workload repository) 데이터들이 저장되는 tablespace 입니다.

awr정보는 default로 1시간에 한번씩 data를 gathering하고 그 정보를 7일동안 저장하게 되어 있습니다.

7일이 지나면 가장 오래된 awr정보를 자동으로 삭제하게끔 되어 있습니다.

awr정보는 7일간 저장되지만 주기적으로 실행하는 table analyze 정보는 default로 31일 동안 저장이 됩니다.

<해결책>

1. select dbms_stats.get_stats_history_retention from dual;
(기본 31일입니다.)

2. exec dbms_stats.alter_stats_history_retention(7);
=> 일주일 주기로 바꿈

3.exec dbms_stats.purge_stats(to_timestamp_tz('10-10-2008 00:00:00 Asia/Seoul','DD-MM-YYYY HH24:MI:SS TZR'));
=> AWR(auto workload repository) 데이터 저장 값입니다. 디폴드 주기 31일 이지만
2008년 10월10일 이전 데이터 삭제 => 날짜 조정 해주시면 됩니다.
oracle 사용 내부 통계 정보로 자동 삭제 주기를 줄인 다음 값을 삭제 하는겁니다.

4.alter table wri$_optstat_histgrm_history enable row movement;

5.alter table wri$_optstat_histgrm_history shrink space;
5번 실행 해서 에러 없을 경우 진행
SQL> alter table wri$_optstat_histgrm_history shrink space;
alter table wri$_optstat_histgrm_history shrink space
*
1행에 오류:
ORA-10631: SHRINK clause should not be specified for this object
=> 에러 날 경우 6번 진행

6. 5번에서 에러 날 경우[index 생성 쿼리 추출 구문 실행]
set long 1000
select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','SYS') from dual;

=>결과
CREATE INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST"
ON "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY"("OBJ#","INTCOL#",SYS_EXTRACT_UTC(
"SAVTIME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSAUX" nologging;


select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_H_ST','SYS') from dual;

=> 결과
CREATE INDEX "SYS"."I_WRI$_OPTSTAT_H_ST"
ON "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY"(SYS_EXTRACT_UTC("SAVTIME")) PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSAUX" nologging;

7. INDEX 삭제 아래 적용 후 재 생성
drop INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST";

drop INDEX "SYS"."I_WRI$_OPTSTAT_H_ST";
=============================================

8. alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;

9. alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;

10. alter table WRI$_OPTSTAT_HISTGRM_HISTORY disable row movement;

11. alter session set workarea_size_policy=manual;

12. alter session set sort_area_size=104857600;

13. 위에서 삭제한 index 생성 쿼리 실행

13-1.
CREATE INDEX "SYS"."I_WRI$_OPTSTAT_H_ST"
ON "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY"(SYS_EXTRACT_UTC("SAVTIME")) PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSAUX" nologging;


13-2.
CREATE INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST"
ON "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY"("OBJ#","INTCOL#",SYS_EXTRACT_UTC(
"SAVTIME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSAUX" nologging;

14.결과 조회


14-1
select occupant_name,space_usage_kbytes/1024 "MB"
from v$sysaux_occupants
order by space_usage_kbytes
/
적용 전
OCCUPANT_NAME                                        MB
--------------------------------------------- ----------

SM/OPTSTAT                                        4.8125
SM/AWR
적용 후

SM/OPTSTAT                                          3.5
SM/AWR

15. SYSAUX Tablespace 사용량 체크

select tablespace_name,sum(bytes/1024/1024) "Free(M)"
from dba_free_space
where tablespace_name = 'SYSAUX'
group by tablespace_name;