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

2 comments:

kalpak1990 said...

Very helpful.

CIIT Noida said...

CIITN is the best SAP training and placement Institute in noida. Contestant will analyze in keeping with modern-day enterprise trends by way of certified and industry experts & SAP training Institute in Noida. They provide a 100% placement help with the best training of IT cousrses.

If you are looking best sap training in Noida then you can join CIITN. One of the premier training and development institute for SAP Training Program in Noida. The company provides 6 weeks/ 6 Months Training for SAP. They provide training in all SAP Modules like SAP HANA,SAP B1, SAP FICO etc. Just come for free demo class and then join SAP and move up in your career .

SAP Training Institute in Noida
sap training in noida
sap institute in noida
sap course in noida
best sap training institute in noida