select
/*+ ordered use_hash(a,c) */
'alter database datafile ''' ||a.file_name|| ''' resize '
||round(a.filesize - (a.filesize - c.hwmsize- 100 ) * 0.8 )|| 'M;' ,
a.filesize,
c.hwmsize
from
(
select file_id,file_name,round(bytes/ 1024 / 1024 ) filesize from dba_data_files
) a,
(
select file_id,round( max (block_id)* 8 / 1024 ) HWMsize from dba_extents
group by file_id) c
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 5 00
'alter database datafile ''' ||a.file_name|| ''' resize '
||round(a.filesize - (a.filesize - c.hwmsize- 100 ) * 0.8 )|| 'M;' ,
a.filesize,
c.hwmsize
from
(
select file_id,file_name,round(bytes/ 1024 / 1024 ) filesize from dba_data_files
) a,
(
select file_id,round( max (block_id)* 8 / 1024 ) HWMsize from dba_extents
group by file_id) c
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 5 00