Found this brilliant script here:
http://www.oracle-wiki.net/startsqlshowrollinfo
prompt This query will monitor rollbacks of transactions after killing a job or session.
prompt If the used undo records/used undo blocks count is going down, then the transaction is rolling back.
prompt If they are going up, then the transaction is still running.
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
/
In my environment, the output looked as follows:
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.
USERNAME SID SERIAL# USED_UBLK USED_UREC SEGMENT_NAME RSSIZE STATUS
-------------------- ----- ------- ---------- ---------- ------------------------------ ---------- ---------------
MYUSERNAME_XX_XXX_ 1194 10616 670875 14088690 _SYSSMU22_1415974968$ 1189502976 ONLINE
The USED_UBLK was dropping, so the ongoing rollback could be confirmed to the users.
