select a.tablespace_name,
b.total/(1024*1024) "total M",
(b.total - nvl(c.free, 0))/(1024*1024) "used M",
round(((b.total - nvl(c.free, 0))/b.total)*100, 2) "% used",
nvl(c.free, 0)/(1024*1024) "free M",
round((nvl(c.free, 0)/b.total)*100, 2) "% free"
from dba_tablespaces a,
(select tablespace_name, sum(bytes) total
from dba_data_files group by tablespace_name) b,
(select tablespace_name, sum(bytes) free
from dba_free_space group by tablespace_name) c
where a.tablespace_name = b.tablespace_name and
a.tablespace_name = c.tablespace_name (+);
b.total/(1024*1024) "total M",
(b.total - nvl(c.free, 0))/(1024*1024) "used M",
round(((b.total - nvl(c.free, 0))/b.total)*100, 2) "% used",
nvl(c.free, 0)/(1024*1024) "free M",
round((nvl(c.free, 0)/b.total)*100, 2) "% free"
from dba_tablespaces a,
(select tablespace_name, sum(bytes) total
from dba_data_files group by tablespace_name) b,
(select tablespace_name, sum(bytes) free
from dba_free_space group by tablespace_name) c
where a.tablespace_name = b.tablespace_name and
a.tablespace_name = c.tablespace_name (+);