Welcome to our guide on how to reduce a datafile in oracle database. Whether you’re a beginner or an expert, you’ll find valuable insights here. Let’s open VirtualBox.
Script to find total size (in MB) that can be reduced from the datafiles in oracle database:
set verify off
set pages 1000
column file_name format a50 word_wrapped
column targetsize format 999,990 heading "Target|Size"
column actualsize format 999,990 heading "Actual|Size"
column Reclaimed format 999,990 heading "Reclaimed|Size"
break on report
compute sum of Reclaimed on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) targetsize,
ceil( blocks*&&blksize/1024/1024) actualsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) Reclaimed
from dba_data_files x,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) y
where x.file_id = y.file_id(+)
/
In the above output we can see what are the datafiles that can be reclaimed and how much. Here we can reclaim space from datafiles oracle 39M total.
If you want to get the script to run directly in the database in order to resize datafile oracle, you can follow our other article Resize datafile oracle script.
