Show Rollback Information 
--
-- Show rollback information.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 99999
 
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND   rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC
/

NOTES

This query will monitor rollbacks of transactions after killing a job or session. If the used undo records/used undo blocks count is going down, then the transaction is rolling back. If they are going up, then the transaction is still running.

rollbacksql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License