Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
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

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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