select /*+ no_merge */

al.tablespace_name,

round(al.currsizemb) currsizemb,

round(al.maxsizemb) maxsizemb,

round(nvl(free.freesizemb, 0)) freesizemb,

round((1 - (nvl(free.freesizemb, 0) + al.maxsizemb - al.currsizemb) /

al.maxsizemb) * 100,

2) usedpct

from (select /*+ no_merge */

t.tablespace_name,

sum(f.bytes) / 1024 / 1024 currsizemb,

sum(case

when f.autoextensible = 'YES' and f.bytes > f.maxbytes then

f.bytes

when f.autoextensible = 'YES' and f.bytes <= f.maxbytes then

f.maxbytes

else

f.bytes

end) / 1024 / 1024 maxsizemb

from dba_tablespaces t, dba_data_files f

where t.tablespace_name = f.tablespace_name

and t.contents = 'PERMANENT'

group by t.tablespace_name) al,

(select tablespace_name, sum(bytes) / 1024 / 1024 freesizemb

from dba_free_space

group by tablespace_name) free

where al.tablespace_name = free.tablespace_name(+);