Query to shrink Datafiles and Reclaim unused Space in Oracle

 Query to shrink Datafiles and Reclaim unused Space in Oracle

Steps to Shrink the datafiles

set verify off

set pages 1000

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

Step1: To Check Database block size

column value new_val blksize

select value from v$parameter where name = 'db_block_size';

Step2: Check how much space can be reclaimed 

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

You can also add condition if needs to be checked for a specific tablespace

Step3: Script to reclaim unused space from the datafiles 

set pages 0

set lines 300

column cmd format a300 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;