[Oracle] How to reclaim wasted space

Hi guys! In this article I’d want to tell you how to reclaim space in Oracle. Below there are some reasons due to which tablespaces can excessively grow:

• Lack of maintenance activity: if you haven’t scheduled the purging script based on the retention period then table sizes can grow excessively and this will cause tablespaces to grow. Once the data is purged, datafiles contain unused space that needs to be reclaimed
• Tables moved to different tablespaces leave behind free unused space in the previous tablespace

Script Shrink Space

--Run the below three select statement one by one --
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
compute sum of savings on report

--To Check Database block size:--
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

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

--Script to reclaim unused space from the datafiles of respective tablespace--
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
/