2

How to get all the users using the undo tablespace and how much?

2 Answers 2

6

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;
1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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