set linesize 200 pagesize 2000
col file_name for a75
select file_name,smallest,currsize,savings
from ( select
a.file_name,
ceil( (nvl(hwm,1)*c.value)/1024/1024 ) smallest,
ceil( blocks*c.value/1024/1024) currsize,
ceil( blocks*c.value/1024/1024) -
ceil( (nvl(hwm,1)*c.value)/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,
v$parameter c
where c.name = 'db_block_size'
and
a.file_id = b.file_id(+)
-- and tablespace_name='USERS'
) where savings >= 1024;
alter database datafile '<>' resize <smallest>m;