oracle-wiki.net - Rollback Monitor

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

Rollback Monitor

-- 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

Rollback Monitor Tool. Use this script to monitor active transactions and track rollback progress. It displays the session, rollback segment, and current undo usage for each active transaction, including the number of undo blocks (USED_UBLK) and undo records (USED_UREC) in use.

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.

Published on

Published 9th July 2026

Page updated

Google Sites

Report abuse