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

Database / Sql

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

락샤리쫑 2012. 12. 3. 15:36

기존에는 테이블을 새로 작성(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 |

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)