To find Percentage Usage of Undo Tablespace which considers Expired Space

SELECT
d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes – a.bytes))/1048576+
u.exp_space),2)
as
max_free_mb, round(((a.bytes – (NVL(f.bytes,0)+
(1024*1024*u.exp_space)))*100/a.maxbytes),2)
used_pct
FROM   sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes))
maxbytes from sys.dba_data_files group by tablespace_name) a,
(select
tablespace_name, sum(bytes) bytes from sys.dba_free_space group by
tablespace_name) f ,
(select
tablespace_name , sum(blocks)*8/(1024) 
exp_space from 
dba_undo_extents
where status NOT IN (‘ACTIVE’,’UNEXPIRED’) 
group by  tablespace_name) u
WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =
f.tablespace_name(+)
AND
d.tablespace_name=u.tablespace_name  AND
d.contents = ‘UNDO’ AND u.tablespace_name = (select UPPER(value)
from
v$parameter where name = ‘undo_tablespace’);
  • April 24, 2012 | 22 views