오라클 Datafile Resize 계산하기

데이터 파일을 줄여야만 하는 상황이 몇가지 있습니다. 디스크 공간이 없을때 데이터를 삭제한 후 데이터파일로 파티션의 사용 공간을 확보하거나, 처음부터 데이터파일을 크게 만들어 두었는데 데이터 양이 많아지지 않고 적은양으로만 유지 된다면 큰 데이터파일을 가지고 있을 필요가 없습니다. 요즘은 디스크 용량이 매우 커지고 가격도 낮아져서 이런 걱정을 안했습니다. 옛날에 장비가 비싸고 디스크 부족에 시달렸을때는 데이터 파일 용량을 줄여 서버의 파티션 용량를 확보 했습니다.

일반적으로 Datafile은 데이터를 삭제한다고 해서 Block을 반환하지 않습니다. Block이 반환되지 않는 부분이 있어서 정확한 사용공간을 계산하려면  아래 쿼리가 필요합니다. 밑에는 그 것을 계산해주는 쿼리 입니다. 출처는 영원한 오라클 유저들의 우상이신 Tom 아저씨네 홈페이지(AskTOM)입니다. Tom 아저씨도 은퇴하신지 좀 되었죠..

maxshrink.sql

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(+) 
/ 

column cmd format a75 word_wrapped 

select 'alter database datafile '''||file_name||''' resize ' || 
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd 
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(+) 
and ceil( blocks*&&blksize/1024/1024) - 
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0 
/

테이블 스페이스에 공간이 부족해서 여유공간을 확보하려고 테이블에 데이터를 지우고 축소를 하려면, 해당 Tablespace가 automatic segment management 가 되어있어야 하고 해당 Table은 row movement가 enable 되어있어야 합니다.

되어있다면

SQL> alter table [table_name] shrink space;
SQL> alter table [table_name] shrink space cascade;

Shrink 작업으로 HWM를 당긴 다음에 Resize 작업을 진행해야 합니다.

Shrink는 온라인 상태에서 가능하나 시간이 오래 걸리고, HWM 당길 테이블들을 기존에 담고 있는 Tablespace로 Move 명령을 하면 Reorg 작업이 가능합니다.

단, Tablespace를 offline 상태에서 해야합니다.