DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Tuesday, April 24, 2012

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');