Monitoring space Usage

posted Jan 9, 2012, 11:41 AM by Sachchida Ojha
1. Tablespace Usage
 
select tbs.tablespace_name,
tot.bytes/1024 totalMB,
tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 usedMB,
sum(nvl(fre.bytes,0))/1024 freeMB,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pctUsed,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
order by 5, 1 ;

 

Comments