Monitoring Rollback Transactions in Oracle

Rollback transactions occur when a database operation is partially completed and needs to be reverted to maintain data integrity. Monitoring rollback progress helps prevent performance issues caused by long-running undo operations.

1. Checking the Status of Rollback Transactions

Use the following query to check the rollback state, completed undo blocks, total undo blocks, and the percentage of rollback completed:


SELECT state, 
       UNDOBLOCKSDONE, 
       UNDOBLOCKSTOTAL, 
       (UNDOBLOCKSDONE / UNDOBLOCKSTOTAL) * 100 AS percent_complete
FROM gv$fast_start_transactions;

State: Indicates if the rollback is ACTIVE or COMPLETED.
UNDOBLOCKSDONE: Number of undo blocks processed.
UNDOBLOCKSTOTAL: Total undo blocks needed for rollback.
percent_complete: Percentage of rollback completion.

2. Estimating Time to Complete Rollback

To predict how long a rollback will take, use the following query:


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT usn, 
       state, 
       undoblockstotal AS "Total Blocks", 
       undoblocksdone AS "Blocks Done", 
       undoblockstotal - undoblocksdone AS "Blocks Remaining",
       DECODE(cputime, 0, 'unknown', 
              SYSDATE + (((undoblockstotal - undoblocksdone) / 
                         (undoblocksdone / cputime)) / 86400)) 
AS "Estimated Completion Time"
FROM v$fast_start_transactions;

usn: Undo segment number.
state: Current rollback state.
Total Blocks: Total undo blocks needed for rollback.
Blocks Done: Completed rollback blocks.
Blocks Remaining: Blocks still to be rolled back.
Estimated Completion Time: Expected time to finish rollback.

3. Identifying Sessions Involved in Rollback

Use the following query to find sessions linked to rollback transactions:


SELECT a.sid, 
       a.username, 
       b.xidusn AS undo_segment, 
       b.used_urec AS undo_records, 
       b.used_ublk AS undo_blocks
FROM v$session a 
JOIN v$transaction b ON a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC;

sid: Session ID executing the rollback.
username: Oracle user executing the transaction.
undo_segment: Undo segment number used.
undo_records: Undo records involved in rollback.
undo_blocks: Number of undo blocks consumed.

4. Action Plan for Long Rollback Transactions

If a rollback is taking too long, consider these steps:
✅ Check if rollback is progressing: If percent_complete is increasing, wait for rollback to complete.
✅ Kill long-running sessions (if necessary):


ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

✅ Check undo tablespace usage:


SELECT tablespace_name, file_id, bytes/1024/1024 AS size_mb 
FROM dba_data_files 
WHERE tablespace_name = 'UNDOTBS1';

✅ Increase undo tablespace size (if rollback is slow due to insufficient space):


ALTER DATABASE DATAFILE '/path_to_undo.dbf' RESIZE 2G;

✅ Enable Fast Start Parallel Rollback for faster undo processing:


ALTER SYSTEM SET fast_start_parallel_rollback = HIGH;

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Monitoring Rollback Transactions in Oracle