select a.file#,
a.name,
c.tablespace_name,
round(a.bytes/1024/1024) CurrentMB,
ceil(HWM *a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM*a.block_size)/1024/1024 ReleaseMB,
‘alter database datafile ‘ || a.FILE# || ‘ resize ‘ ||
round(ceil(HWM*a.block_size)/1024/1024+5)||‘M;‘ ResizeCmd
 from v$datafile a,
(SELECT file_id,MAX(block_id+blocks-1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b,
dba_data_files c
 where a.file# = b.file_id(+)
And (a.bytes - HWM * a.block_size) >0
and a.FILE#=c.file_id
and c.tablespace_name not in (‘SYSTEM‘,‘SYSAUX‘)
and c.tablespace_name not like ‘%UNDO%‘
 order by 6 desc;

select file_name,bytes/1024/1024/1024 as GB from dba_data_files

alter database datafile 4 resize 1363M;
alter database datafile 8 resize 574M;
alter database datafile 7 resize 6M;
alter database datafile 9 resize 6M;
alter database datafile 6 resize 16M;
alter database datafile 5 resize 318M;

select file_name,bytes/1024/1024/1024 as GB from dba_data_files