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

초기 테이블스페이스 용량을 과도하게 설정하여 비효율 발생시, 미사용 공간을 OS에 반환 할수 있다.

 

테이블스페이스 공간내에 HWM(High Water Mark) 찾고 Datafile을 Reduce 한다.

ex) 최초 구성시 100GB 할당 했으나 최대 1GB 사용중(HWM) 이라면 99GB를 OS에 반환이 가능.

 

( 방법 1)

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/95)*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
;

 

( 방법 2)

-- (문서 ID 1600774.1)
 
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
 
SELECT value FROM v$parameter WHERE name = 'db_block_size';
/
 
SELECT file_name
     , CEIL((NVL(hwm,1)*&&blksize)/1024/1024) smallest
     , CEIL(blocks*&&blksize/1024/1024) currsize
     , CEIL(blocks*&&blksize/1024/1024) - CEIL((NVL(hwm,1)*&&blksize)/1024/1024) savings
FROM dba_data_files a,
( SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
WHERE a.file_id = b.file_id(+) 
ORDER BY savings desc
/