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