Resize tablespace (Shrink tablespace)

By using this it will generate a command to resize after calculating the old tables space to new table space.

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
   a.tablespace_name,
    a.file_name,
   a.bytes/1024/1024 file_size_MB,
    (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
     (select file_id,max(block_id) maximum        
      from dba_extents        
      group by file_id) b,
      dba_extents c,
     (select value db_block_size        
      from v$parameter        
      where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);


Example output -:

'ALTERDATABASEDATAFILE'||''''||FILE_NAME||''''||'RESIZE'||ROUND(HIGHWATER+2)||''
--------------------------------------------------------------------------------
alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_cachetbs_807kzmyb_.dbf' resize 18 m;







alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_cmstbs_807kxdw4_.dbf' resize 7 m;

alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_dbaudit__807kwjwy_.dbf' resize 246m;

alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_dbaudit__807kwy3f_.dbf' resize 154m;