Shrink기능을 통한 테이블사이즈 줄이기

화화려한청춘·2014년 4월 14일·조회 23,089

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

로그인 후 댓글을 남길 수 있습니다.

빅토르최· 2014년 4월 14일
정말 저한테 필요한 정보였네요. 주기적으로 delete 하여 데이터를 지우고 있는데 reorg 를 해야 free space 가 확보되므로 주기적으로 alter table 을 이용하여 reorg 를 하고 있습니다. 일단 쿼리를 따라서 해보았습니다. 잘 나옵니다. 그런데 select * from table(dbms_space.asa_recommendations()); 는 결과가 너무 많이 나와서 최소 컬럼만 넣고 다시 조회했습니다. SQL> select segment_type, segment_owner, allocated_space, used_space, reclaimable_space from table(dbms_space.asa_recommendations()); INDEX PARTITION TABLES_TEST 63963136 40133785 23829351 INDEX PARTITION TABLES_TEST 83886080 59309225 24576855 INDEX PARTITION TABLES_TEST 67108864 41869823 25239041 INDEX PARTITION TABLES_TEST 45088768 28239298 16849470 INDEX PARTITION TABLES_TEST 49283072 31015900 18267172 INDEX PARTITION TABLES_TEST 57671680 36337067 21334613 INDEX PARTITION TABLES_TEST 53477376 33449208 20028168 INDEX PARTITION TABLES_TEST 62914560 39388341 23526219 INDEX PARTITION TABLES_TEST 59768832 37451810 22317022 INDEX PARTITION TABLES_TEST 30408704 19053787 11354917 INDEX PARTITION TABLES_TEST 42799487 26731208 16068279 잘 나옵니다. 마지막으로 Oracle 9i 버전에서는 역시 안됩니다. SQL> select job_name, program_name from dba_scheduler_jobs where job_name like '%SPACE%'; select job_name, program_name from dba_scheduler_jobs where job_name like '%SPACE%' * ERROR at line 1: ORA-00942: table or view does not exist
화화려한청춘· 2014년 4월 18일
필요한 정보라고 하시니 힘이 납니다..혼자만의 공간이라고 생각했는데...감사합니다 : )

Shrink기능을 통한 테이블사이즈 줄이기

댓글 2