Data file resizing

Hello friends, this is a vast topic to be discussed, I will try to keep it short and understandable, how much space can you gain by resizing the data file, reclaiming the un-used space. It depends on different environments to hold the different amount of free space for different tablespaces. Many go with the 70% usage, while many go with the 85%. Be sure that extents can still be allocated in each tablespace. There may be enough free space, but it may be too fragmented to be used. The query which can be used is:

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
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 ) 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(+) order by savings desc
/
*********************************************************************************
The above query has been derived from the below work-around. We are concentrating on the datafiles here, rather than the tablespaces, because it is possible that the tablespace might have one or multiple data files. Plus, only space that is after the last data block can be de-allocated. So the next step should be to find the last data block. To check this, we use the command:

select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id;


Now that the command to find the last data block has been figured out, it is time to find the free space in each file above that last data block:

Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id;