Oracle Datafile 사용 공간 줄이기

데이터 파일의 저장공간을 넉넉히 갖고 운영할 수 있으면 좋겠지만, 대부분의 사이트 들은 한정된 disk 공간으로 운영을 합니다.  Disk의 가격도 만만치 않거니와 향후 발생할 데이터 양을 너무 빡빡하게 계산해 놓으면 이 한정된 disk를 알뜰하게 DBA들이 사용해야 하죠. 

Disk의 공간을 줄이기 위해 compress table이나 table re-org 작업, tablespace shrink 작업들을 하게되는데 오늘은 Bulletin 10165의 내용을 정리합니다. 

tablespace shrink 작업은 extent가 할당되지 않은 공간 중 마지막 extent 부분 부터 줄일 수 있습니다. 즉, tablespace가 처음 할당 되고 얼마 되지 않은 table 들이 생성됬을 경우 꽤 짭짭할 공간을 줄일 수 있겠지만, 이미 사용한지 좀 된 tablespace의 경우에는 중간중간 extent가 할당되어 얼마 못 줄이게 됩니다. 이런 경우에는 table을 drop하고 재 생성하는 등의 re-org 작업이 선행 되거나 tablespace를 이리저리 건너 뛰면서 줄여야 됩니다. 

그럼 tablespace를 resize 할 수 있는 max size를 어떻게 구할 수 있을까요? 

dba_extents라는 view를 보면 datafile 별로 extent가 할당된 정보가 나옵니다. 이 할당된 extent의 마지막 위치를 파악해서 바로 그 이후로 size를 지정하면 됩니다.  다음의 공식은 bulletin 10165에 나와 있는 공식입니다. 

1) 줄이고자하는 file id를 확인합니다.

SQLPLUS> select file_id, file_name from dba_data_files;

2) 얼마를 사용했는지 확인합니다.

SQLPLUS> select block_id, blocks from dba_extents where file_id='FILE_ID' order by block_id;

FILE_ID 대신에 줄이려는 file id를 기술합니다.

3)현재 사용중이 db block size를 확인합니다.

SQLPLUS> show parameter db_block_size

2)에서 조회된 마지막의 block_id, blocks 값을 가지고 계산합니다. 

block_id * db_block_size + blocks * db_block_size 의 결과에 한 block더한 값만큼만 줄이는 것이 가능합니다. 

만일 마지막으로 조회된 값이 block_id가 578261, blocks가 515, db_block_size가 2048 일 경우  

578261*2048 + 515*2048 =1185333248로 최소한 1.2GB이상은 써야 합니다. 

4) 실제 datafile을 줄입니다.

SQLPLUS>alter database datafile '/oracle/dbs/toolsORA.dbf' resize 1200M;

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!