--
-- rollback_activity.sql
-- by Brian Peasland
-- 29 December 2000
--
-- This script will show what users and their SQL statements
-- are currently using a rollback segment.
--
COLUMN name FORMAT a6
SET linesize 120

PROMPT Rollback Activity
PROMPT Shows current activity associated with a rollback segment

SELECT r.name,s.sid,s.serial#,s.username,t.sql_text
FROM   v$rollname r, v$session s, v$sqltext t, v$transaction x
WHERE  r.usn = x.xidusn AND  s.taddr = x.addr 
       AND s.sql_address = t.address AND  s.sql_hash_value = t.hash_value
ORDER BY r.name,s.sid,t.piece;