Can anybody help me on "what all users are using the undo tablespace and how much" in oracle database 11g r2. any pointers would be much appreciated.
This shows you the amount of undo size used by each session that is currently active.
select s.sid, s.username, sum(ss.value) / 1024 / 1024 as undo_size_mb from v$sesstat ss join v$session s on s.sid = ss.sid join v$statname stat on stat.statistic# = ss.statistic# where stat.name = 'undo change vector size' and s.type <> 'BACKGROUND' and s.username IS NOT NULL group by s.sid, s.username;
Undo is not used by users directly but by statements which modify many database blocks. Try to search for session which generates a lot of redo.
select sid, serial#, value from v$session join v$sesstat using(sid) where statistic# = (SELECT statistic# FROM v$statname WHERE name = 'redo size');
Or for active transactions:
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'
Or look at the view V$UNDOSTAT.