Shrink a datafile

column value new_val block_size
select value from v$parameter where name = 'db_block_size';

select 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*&&block_size)/1024/1024 )||'M;' resize_command
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 file_name;

RESIZE_COMMAND
--------------------------------------------------------------------------------
alter database datafile 'B:\ORADATA\PSAPF0P.DBF' resize 800M;
alter database datafile 'B:\ORADATA\PSAPUNDO.DBF' resize 53M;
alter database datafile 'B:\ORADATA\SYSAUX01.DBF' resize 574M;
alter database datafile 'B:\ORADATA\USERS01.DBF' resize 602M;
alter database datafile 'C:\ORACLE\ORADATA\TST\PSAPSR3.DBF' resize 1M;
alter database datafile 'C:\ORACLE\ORADATA\TST\SYSTEM01.DBF' resize 369M;