col tsname format a16 justify c heading 'Tablespace'
col nfrags format 999,990 justify c heading 'FreeFrags'
col mxfrag format 999,999,990 justify c heading 'LargestFrag (KB)'
col totsiz format 999,999,990 justify c heading 'Total(KB)'
col avasiz format 999,999,990 justify c heading 'Available(KB)'
col pctusd format 990 justify c heading 'PctUsed'
select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from
dba_data_files total,
dba_free_space free
where
total.tablespace_name = free.tablespace_name(+)
group by
total.tablespace_name,
total.bytes;
