rollbacks_in_use.sql
Download Script
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