column "ID#" format 999
column "Rollback Segment" format a10
column "User" format a12
column "OS User" format a9
column "Host" format a9
column "Slot" format a4
break on "ID#" skip 1
ttitle "To kill a session: ALTER SYSTEM KILL SESSION 'sid,serial#';"
set pagesize 100


select c.usn "ID#", 
       c.name "Rollback Segment", 
       to_char(d.xidslot) "Slot",
       b.username "User",
       b.sid "Session ID", 
       b.serial# "Serial#", 
       b.osuser "OS User",
       b.machine "Host"
from v$newlock a, v$session b, v$rollname c, v$transaction d
where a.type='TX'
and   a.sid=b.sid
and   b.taddr=d.addr
and   c.usn=trunc(a.id1/1048596)*16+trunc((mod(a.id1,1048596))/65536)
order by c.usn, d.xidslot
/
ttitle off
