bytes,2) used_pct
from
(select
tablespace_name, sum(bytes) sumbytes
from dba_free_space group by tablespace_name) f,
(select tablespace_name, sum(bytes) sumbytes
from dba_data_files group by tablespace_name) d
where f.tablespace_name(+) = d.tablespace_name
order by d.tablespace_name) b
where a.tablespace_name=b.tablespace_name
union all
select b.tablespace_name tablespace,
b.total_m,
b.free_m,
b.used_m,
b.used_pct
from
dba_tablespaces a,
(select
d.tablespace_name tablespace_name,
round((d.sumbytes/1024/1024),2) total_m,
round((d.sumbytes/1024/1024),2)-round(decode(f.sumbytes,null,0,f.
sumbytes)/1024/1024,2) free_m,
round(decode(f.sumbytes,null,0,f.sumbytes)/1024/1024,2) used_m,
round(decode(f.sumbytes,null,0,f.sumbytes)*100/d.sumbytes,2)
used_pct
from
(select
tablespace_name, sum(bytes_used) sumbytes
-- sum(bytes_cached) sumbytes
from v$temp_extent_pool group by tablespace_name) f,
(select tablespace_name, sum(bytes) sumbytes
from dba_temp_files group by tablespace_name) d
where f.tablespace_name(+) = d.tablespace_name
order by d.tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 5;
TTITLE OFF