select /*+ ordered use_hash(a,b,c) */
a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize
from (select file_id, file_name, round(bytes / (1024 * 1024)) filesize
from dba_data_files) a,
(select file_id, round(sum(dfs.bytes) / (1024 * 1024)) freesize
from dba_free_space dfs
group by file_id) b,
(select file_id,TABLESPACE_NAME, round(max(block_id) * 8 / 1024) HWMsize
from dba_extents
group by TABLESPACE_NAME, file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id
order by unsedsize_belowhwm desc
a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize
from (select file_id, file_name, round(bytes / (1024 * 1024)) filesize
from dba_data_files) a,
(select file_id, round(sum(dfs.bytes) / (1024 * 1024)) freesize
from dba_free_space dfs
group by file_id) b,
(select file_id,TABLESPACE_NAME, round(max(block_id) * 8 / 1024) HWMsize
from dba_extents
group by TABLESPACE_NAME, file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id
order by unsedsize_belowhwm desc