Scripts for checking undo usage

Note : - Replace UNDOTBS1 with your UNDO TABLESPACE NAME  and execute the following statements

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES)/1024/1024 "Mb", COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;
select max(maxquerylen),max(tuned_undoretention) from v$undostat;
select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr=b.ses_addr;
select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS1';

select segment_name,
 round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
 round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
 round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
 NO_OF_EXTENTS
 from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
 from DBA_UNDO_EXTENTS
 where status='ACTIVE' and tablespace_name = 'UNDOTBS1'
 group by segment_name
 union
 select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
 from DBA_UNDO_EXTENTS
 where status='UNEXPIRED' and tablespace_name = 'UNDOTBS1'
 group by segment_name
 union
 select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
 from DBA_UNDO_EXTENTS
 where status='EXPIRED' and tablespace_name = 'UNDOTBS1'
 group by segment_name
 ) group by segment_name, NO_OF_EXTENTS order by 5 desc;

select sum(blocks),count(*) extents,segment_name from DBA_EXTENTS
where tablespace_name = 'UNDOTBS1' group by segment_name order by 2;