오라클 datafile size 줄이기

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 오라클 datafile size 줄이기

컬럼 설정

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

db block size 확인

column value new_val blksize
column value for a10
select value from v$parameter where name = 'db_block_size'
/
 
VALUE
----------
8192

데이터 파일 당 할당된 size와 사용중인 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(+)
/
 
                                                  Smallest
                                                    Size    Current    Poss.
FILE_NAME                                           Poss.    Size    Savings
-------------------------------------------------- -------- -------- --------
/dev/raw/raw6                                        497      699      202
/dev/raw/raw7                                        699      699        0
/dev/raw/raw10                                       145      199       54
/dev/raw/raw11                                       522      699      177
/dev/raw/raw8                                        213      699      486
/oracle/app/oracle/oradata/ts02.dbf                  322    1,024      702
/oracle/app/oracle/oradata/movets02.dbf                1    1,024    1,023
                                                                     --------
sum                                                                  2,644

위 결과에 따라 사이즈 줄이는 쿼리 생성

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
/
 
CMD
---------------------------------------------------------------------------
alter database datafile '/dev/raw/raw6' resize 497m;
alter database datafile '/dev/raw/raw10' resize 145m;
alter database datafile '/dev/raw/raw11' resize 522m;
alter database datafile '/dev/raw/raw8' resize 213m;
alter database datafile '/oracle/app/oracle/oradata/pymts02.dbf' resize 322m;
alter database datafile '/oracle/app/oracle/oradata/movets02.dbf' resize 1m;

참조 : https://cafe.naver.com/prodba/7130

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:766625833673