Monitoring Rollback Segments



My OraFAQ Blog

Contact me

What rollback segments are in use, and by whom?

select /*+ rule */,, p.spid, nvl(p.username,'no transaction') 
nvl(l.username,'no transaction') username
 (select si.username, lo.* from v$lock lo, v$session si where lo.sid=si.sid) l,
 v$process p, v$rollname r
where   l.sid = and     trunc(l.id1(+)/65536) = r.usn 
and     l.type(+) = 'TX' and     l.lmode(+) = 6
order by

select s.sid, s.username, "ROLLBACK SEG"
  from v$session s, v$transaction t, v$rollname r
 where s.taddr=t.addr
  and  t.xidusn = r.usn;

At what position in the RBS are we?

select curext, curblk from v$rollstat where usn=2;

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...