[Tablespace] HWM 찾아서 Datafile 줄이기 - 2

이전 글 "HWM 찾아서 Datafile 줄이기 - 1" 에서는 

테이블스페이스 공간내에 HWM(High Water Mark) 찾아 Datafile을 reduce 하는 방법 소개했었다.

단점은 HWM가 늘어난 상황에서 안쪽에 Free 공간이 많더라도 대응 방법은 없다.

 

HWM 안쪽에 Free 공간이 많을때 HWM에 걸치고 있는 오브젝트들을 Move 함으로써  HWM 자체를 낮출수 있다.

 

[ 테스트 ]

01. 테스트 데이터 생성

-- (1) Create Tablespace
DROP TABLESPACE TS_ORAKIM_DATA INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
CREATE TABLESPACE TS_ORAKIM_DATA DATAFILE 'C:\oracle\oradata\orakim\TS_ORAKIM_DATA2.DBF' SIZE 100M;
 
-- (2) Create Test Table
DROP TABLE T1;
CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR2(100)) TABLESPACE TS_ORAKIM_DATA;
INSERT INTO T1 SELECT LEVEL,'SAMPLE TEXT OF LINE'||LEVEL FROM DUAL CONNECT BY LEVEL <= 300000;
COMMIT;
 
DROP TABLE T2;
CREATE TABLE T2 (C1 NUMBER, C2 VARCHAR2(100)) TABLESPACE TS_ORAKIM_DATA;
INSERT INTO T2 SELECT LEVEL,'SAMPLE TEXT OF LINE'||LEVEL FROM DUAL CONNECT BY LEVEL <= 800000;
COMMIT;
 
DROP TABLE T3;
DROP INDEX IX_T3_01;
CREATE TABLE T3 (C1 NUMBER, C2 VARCHAR2(100)) TABLESPACE TS_ORAKIM_DATA;
INSERT INTO T3 SELECT LEVEL,'SAMPLE TEXT OF LINE'||LEVEL FROM DUAL CONNECT BY LEVEL <= 100000;
CREATE INDEX IX_T3_01 ON T3 (C1) TABLESPACE TS_ORAKIM_DATA;
COMMIT;

 

02. 테이블스페이스 Extents Map 확인 (by Orange)

T1 테이블의 Extents Map

T2 테이블의 Extents Map

T3 테이블의 Extents Map (HWM)

03. HWM 아래쪽으로 Free 공간 생성

-- 먼저 생성된 T1,T2 테이블 Truncate 로 HWM 아래쪽에 Free 공간 생성
TRUNCATE TABLE T1;
TRUNCATE TABLE T2;

HWM (T3 테이블) 아래 Free 공간 발생

04. T3 테이블 Move 수행으로 HWM 낮추기

SELECT * 
FROM  
(
    SELECT OWNER
         , DECODE(SEGMENT_TYPE,'TABLE',SEGMENT_NAME,TABLE_NAME) SEGMENT_NAME
         , DECODE(SEGMENT_TYPE,'TABLE','TABLE','INDEX') SEGMENT_TYPE
         , 'ALTER ' ||SEGMENT_TYPE || ' '|| OWNER || '.' || SEGMENT_NAME || DECODE( SEGMENT_TYPE, 'TABLE', ' MOVE', ' REBUILD' ) || ' TABLESPACE SYSAUX' || ' ONLINE;' COMMAND
    FROM DBA_SEGMENTS,(SELECT TABLE_NAME, INDEX_NAME FROM DBA_INDEXES )
    WHERE SEGMENT_TYPE IN ( 'TABLE', 'INDEX' )
    AND OWNER IN ('ORAKIM')
    AND TABLESPACE_NAME = 'TS_ORAKIM_DATA'
    AND SEGMENT_NAME = INDEX_NAME (+)
)
WHERE SEGMENT_NAME='T3'
;
 
ALTER TABLE ORAKIM.T3 MOVE TABLESPACE SYSAUX ONLINE;
ALTER INDEX ORAKIM.IX_T3_01 REBUILD TABLESPACE SYSAUX ONLINE;

 

05. HWM 확인 및 Datafile Reduce 수행

SELECT a.tablespace_name AS "tablespace"
     , a.file_name AS "data file"
     , ROUND(a.avail/1024,1) AS "file size (gb)"
     , ROUND(NVL(a.avail/1024 - b.free/1024,0),1) AS "used (gb)"
     , ROUND((NVL(hwm,1)*blksize)/1024/1024/1024,1) AS "hwm (gb)"
     , ROUND(NVL(b.free/1024,0),1) AS "free (gb)"
     , (100-NVL(ROUND(((free/avail)*100),1),0)) AS "data used (%)"
     , ROUND((ROUND((NVL(hwm,1)*blksize)/1024/1024) / ROUND(blocks*blksize/1024/1024)) * 100,1) AS "hwm used (%)"
     , ROUND(blocks*blksize/1024/1024/1024,1) - nvl(ROUND((hwm*blksize)/1024/1024/1024,1 ),ROUND( blocks*blksize/1024/1024/1024,1)) AS "savings (gb)"
     , RPAD(' '|| RPAD('*',ROUND((avail-free)/avail*10,0), '*'),11,'-') AS "used_grp"
     , 'alter database datafile '''|| file_name || ''' resize ' || ceil((nvl(hwm,1)*(blksize))/1024/1024/90)*100  || 'm;' "command" -- hwm의 95% 수준으로 resize!!
FROM 
( SELECT tablespace_name, file_id, file_name, ROUND(SUM(bytes/(1024*1024)),3) avail, blocks FROM dba_data_files GROUP BY tablespace_name, file_id, file_name,blocks ) a, 
( SELECT tablespace_name, file_id, round(SUM(bytes/(1024*1024)),3) free FROM dba_free_space GROUP BY tablespace_name, file_id ) b, 
( SELECT file_id, MAX(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) c, 
( SELECT value blksize FROM v$parameter WHERE name = 'db_block_size' ) d
WHERE a.file_id = b.file_id (+)
AND   b.file_id = c.file_id (+)
AND   a.tablespace_name = 'TS_ORAKIM_DATA'
ORDER BY "savings (gb)" DESC
;
 
alter database datafile 'C:\ORACLE\ORADATA\ORAKIM\TS_ORAKIM_DATA2.DBF' resize 20m;