rollback segments with NO TRANSACTION

From: rpr <renu_p_r_at_yahoo.com>
Date: 6 Nov 2003 16:54:33 -0800
Message-ID: <71802af1.0311061654.487a7c40@posting.google.com>

Hello,
  I am looking at rollback segment usage and a lot of them show as being used but I do not see many transactions (V$TRANSACTION). Is this normal? There is lot of waits in v$waitstat for 'undo header' and 'undo block'. How do I avoid that? :

I am using query and see a lot of rollback segments with 'NO TRANSACTION': SELECT r.name , p.pid , p.spid , NVL (p.username, 'NO TRANSACTION'),

         s.sid, s.username, p.terminal
FROM v$lock l, v$process p, v$rollname r, v$session s WHERE l.sid = p.pid(+)

AND      TRUNC (l.id1(+)/65536) = r.usn
AND      l.type(+) = 'TX'
AND      l.lmode(+) = 6
AND      p.addr = s.paddr (+)

ORDER BY r.name

Thank You Received on Thu Nov 06 2003 - 18:54:33 CST