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, 
       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,
       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.

  • Thanks for a quick reply ivan, i was referrinng to session when i said users, let me rephrase my question."How can i view what session/query is using what volume of undo tablespace". Appreciate your help. – Hitesh Chouhan Apr 8 '13 at 9:40
  • I can't (but not 100% sure). Simply undo is not only written by user's session but also by DBW(db writter) process, which asynchronously scans buffer cache. If you want to find any "culprit" search for statements which generate a lot of redo or for transactions, which occupy space in UNDO. – ibre5041 Apr 8 '13 at 11:35
  • Appreciate your help ivan. I will work towards Your suggestion over transactions to undo mapping. Thanks – Hitesh Chouhan Apr 8 '13 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.