Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.5 to 11.2.0.3 - Release: 9.2 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 17-Jan-2012***

Goal

How do you determine which users are using and how much UNDO is being used?

Solution

Use the next query:

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;

EXAMPLE:

SQL> select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
2 from v$session a, v$transaction b
3 where a.saddr=b.ses_addr;

SID SERIAL# USERNAME USED_UREC USED_UBLK
---------- ---------- ------------------------------ ---------- ----------
21 7856 KBCOOK 1 1

With:
USED_UBLK = Number of undo blocks used
USED_UREC = Number of undo records used