Reclaiming Unused Space in Datafiles

Sometimes you need to shrink tablespace datafiles to reclaim unused space in Oracle. By Shrinking you can reclaim space from the datafiles and as a result file system or ASM storage will be freed up and available for the other activity.

If you delete data from the table then it cause fragmentation and it will not release the space from the datafiles of respective tablespace. In case of fragmentation you need to defragment the tables.

Most Effective Way to Delete Huge Records from a Table in Oracle

In case of truncate, if you truncate some big size tables then it will release space from the datafiles but space will not be visible at the ASM or file system level because high watermark of the datafiles(max size) will not shift to backward position. But internally space will be there inside the datafiles. You can check from DBA_FREE_SPACE.

How to Find and Remove Table Fragmentation in Oracle Database

In both the cases you need to shrink datafiles to make the space visible at ASM or File System level.

Steps to Reclaim Unused Space from the Datafiles

 STEP 1: Check the database block size.
column value new_val blksize
 select value from v$parameter where name = 'db_block_size'
 /

STEP 2: Run the datafile shrink script.
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
/

STEP 3: Above script will ask for the block size, enter block size from step 1.

STEP 4: Above script will generate resize datafiles script, copy in notepad and remove system, sysaux, temp, undo, users tablespace and run the generated script.

STEP 5: After shrinking activity check the free space.
df -h  -- for Linux
SQLPLUS> select NAME, TOTAL_MB/1024, FREE_MB/1024 from v$asm_diskgroup;
 Or from ASMCMD just type:
ASMCMD> lsdg