Shrink기능을 통한 테이블사이즈 줄이기
Oracle 10g에서 Online Segment Shrink 기능이 추가되면서, 기존 Reorg방식에 비해 간편하게 Segment의 크기를 줄일 수 있게 되었다. Shrink 기능은 HWM(High Water Mark)를 줄일 수 있는 기능이며, 이 기능을 활용하면 공간을 좀더 효율적으로 활용할 수 있고, 쿼리의 속도 개선에도 도움이 된다.
* 사전 확인 사항
inir.ora 파일내에 compativle =10.2 이상 확인
AUTO Segment Space Managed (ASSM) Tablespace에 존재하는 세그먼트 대상
* Shrink 대상이 되는 Segment(Table/Index/Partition) 목록 추출
아래와 같이 dba_scheduler_job 뷰를 조회해보면, auto_space_advisor_job이라는 스케쥴 작업이 등록되어 있고,
이 작업은 auto_space_advisor_prog이라는 프로그램을 수행한다.
select job_name, program_name
from dba_scheduler_jobs
where job_name like '%SPACE%';
-----------------------------------------------------
JOB_NAME : AUTO_SPACE_ADVISOR_JOB
PROGRAM_NAME : AUTO_SPACE_ADVISOR_PROG
auto_space_advisor 프로그램은 dbms_space.auto_space_advisor_job_proc이라는 프로시저를 수행한다.
select program_name, program_action
from dba_scheduler_programs
where program_name = 'AUTO_SPACE_ADVISOR_PROG';
------------------------------------------------------------
PROGRAM_NAME : AUTO_SPACE_ADVISOR_PROG
PROGRAM_ACTION : dbms_space.auto_space_advisor_job_proc
Auto Space Advisor는 Advisor의 한 종류로 10g에서 새로 추가된 Advisor이다.
이 Advisor는 Tablespace/Segment의 공간 사용 정도를 분석하여 공간을 절약하기 위한 가이드역할을 한다.
- dbms_space.asa_recommendations 이용
select * from table(dbms_space.asa_recommendations());
-dbms_space.asa_recommendations을 이용하여 다음과 같이 shrink 대상 Segment명, 타입과 축소가능크기가 조회된다.
-------------------------------------------------------------
SEGMENT_NAME : BIG_TABLE
SEGMENT_TYPE : TABLE
ALLOCATED_SPACE : 300619974
USED_SPACE : 273395165
RECLAIMABLE_SPACE : 27224809
RECOMMENDATIONS : BIG_TABLE 테이블의 축소 가능크기는 27224809바이트
* 테이블 축소하기
1. 테이블과 HWM을 Shrink시킨다. 해당 테이블의 공간만 Tablespace로 환원
SQL> alter table scott.BIG_TABLE shrink space
2. 테이블 및 관련된 인덱스를 모두 Tablespace로 환원하여, Shrink 시킨다.
SQL> alter table scott.BIG_TABLE shrink space cascade;
3. 테이블을 shrink 시키지만, HWM (High Water Mark)을 shrink 시키지 않음
SQL> alter table scott.BIG_TABLE shrink space compact;
* Shrink를 적용시킬 수 없는 테이블
UNDO segments
temporary segments
clustered tables
tables with a column datatype LONG
LOB Index
IOT mapping tables and IOT overflow segments
tables with MVIEWS with on commit
tables with MVIEWS which are based on ROWIDs