How much rollback segment used at particular time..?

[skdas]

Hi ,

Does any one have any script , to find out how much Rollback segment used by which user, which rollback segment is used, how much space is available etc.

Thanks..

skdas

[st2000]

select substr(r.name,1,10) name, r.usn, e.extents, s.sid, substr(s.username,1,10) username, s.logon_time,
s.status, t.start_time, t.status, t.used_ublk, substr(s.program,1,20) program, s.command
from v$transaction t ,v$rollname r , v$rollstat e, v$session s
where r.usn = t.xidusn
and t.xidusn = e.usn
and t.addr = s.taddr
order by r.name

See v$rollstat to see how extents are used by rssize, hwm and extends.. for active transactions, use xacts > 0

Thanks, ST2000