Doc

      Name:     rolbusrs.sql

      Author:   Mark Gurry

      This script lists information on who is accessing the various rollbacks as of NOW!

#
column "Rollback Segment Name" format a18;
column  "Oracle User Session" format a40;

set pagesize 9999
ttitle 'Current Rollback Segment Usage'
set heading off

spool rolbusrs.lis

select r.name "Rollback Segment Name",
       p.spid "Process ID", 
       s.username||'('||l.sid||')' "Oracle User Session",
       sq.sql_text
  from v$sqlarea sq, v$lock l, v$process p, v$session s, v$rollname r
 where l.sid = p.pid(+)
   and s.sid = l.sid
   and trunc(l.id1(+) / 65536) = r.usn
   and l.type(+) = 'TX'
   and l.lmode(+) = 6
   and s.sql_address = sq.address
   and s.sql_hash_value   = sq.hash_value
 order by r.name
/

spool off