[Oracle] 10g 공간 줄일수 있는 테이블 찾기와 Shrink 실행하기

 기존에는 테이블을 새로 작성(reorg)  작업을 통해 커진 테이블 스페이스 크기를 복구 가능하였으나 10g 부터는 Shrink 기능을 이용하여 손쉽게 테이블을 줄일수 있게 되었다.

Shrink 기능은 HWM(High Water Mark)를 줄일수 있는 기능이다.

- 사전 확인 사항

init.ora 파일내에 compatible = 10.2 이상인것을 확인

ASSM (Automatic Segment Space Management) 테이블 스페이스여만 가능

각 테이블 조회 방법은

 

SELECT OBJ.OWNER
           , OBJ.TABLE_NAME, OBJ.TABLESPACE_NAME
           , (CASE WHEN NVL(IDX.CNT, 0) < 1 THEN 'YES' ELSE 'NO' END) SHRINKABLE
   FROM DBA_TABLES OBJ,
             (SELECT TABLE_NAME, COUNT(ROWNUM) CNT
                 FROM DBA_INDEXES
               WHERE INDEX_TYPE LIKE 'FUN%'
               GROUP BY TABLE_NAME) IDX
 WHERE OBJ.TABLE_NAME = IDX.TABLE_NAME(+)
     AND OBJ.TABLESPACE_NAME = :TABLESPACE_NAME;

 

쿼리를 통해 (테이블 스페이스 명을 인자값으로) 확인 가능하다.

 

 

1. 테이블 검색 방법

SELECT * FROM TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS());

아래와 같이 테이블의 DBA_SEGMENTS에서 볼수 있는 크기와 축소 가능 크기가 조회된다.

 

SEGMENT_OWNER | SEGMENT_NAME | SEGMENT_TYPE | PARTITION_NAME |

ALLOCATED_SPACE | USED_SPACE | RECLAIMABLE_SPACE
MAX_TI | SMR_IF_LOG       | TABLE  | | 6761793074 | 5910149080 | 851643994
MAX_TI | SMR_IF_LOG_I01 | INDEX  | |   478455081 |   420139142 | 58315939
MAX_TI | SMR_IF_LOG_I02 | INDEX  | |   411921243 |   398204079 | 13717164

 

2. 축소하기

가장 먼저 행들을 이동 가능하도록 변경한다.

위의 SMR_IF_LOG 테이블을 기준으로 설명하겠다.

ALTER TABLE SMR_IF_LOG ENABLE ROW MOVEMENT;

--> 반대로는 ALTER TABLE SMR_IF_LOG DISABLE ROW MOVEMENT; 이다..

이제 SHRINK 기능을 수행할수 있으며, 다음 3가지 형태로 가능하다

 

① ALTER TABLE SMR_IF_LOG SHRINK SPACE; 
    -- SMR_IF_LOG 테이블의 공간만 TABLESPACE로 환원

 

② ALTER TABLE SMR_IF_LOG SHRINK SPACE CASCADE;
    -- 모든 관련 OBJECT까지 TABLESPACE으로 공간 환원

 

③ ALTER TABLE SMR_IF_LOG SHRINK SPACE COMPACT;
   -- 행들만 이동시킴.

 

 

※ SHRINK 를 적용 시킬  없는 테이블
UNDO segments
temporary segments
clustered tables
tables with a column of datatype LONG
LOB indexes
IOT mapping tables and IOT overflow segmnets
tables with MVIEWS with ON COMMIT
tables with MVIEWS which are based on ROWIDs.

 

 

[출처] 웹 개발과 관련된 모든 것들 (http://web-dev.tistory.com/116)