How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,

Using following query, we can estimate # of rows resides in each USN (Rollback Segment) and who fired that transactions.

New modified GCU.sql on 16-JUN-2006

/* Get Curent USN */

column start_dt format a20

set lines 132

select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status,

to_char(b.start_date, ‘DD-MON-RRRR:HH24:MI:SS’) start_dt, b.status tx_status,

c.sid, c.sql_id, b.used_ublk, b.used_urec,

d.sql_text

from v$rollstat a, v$rollname a1, v$transaction b, v$session c, v$sqlarea d

where a.usn = b. xidusn

and a.usn = a1.usn

and b.ses_addr = c.saddr

and c.sql_id = d.sql_id;