Undo Tracker

Monitor long-running rollback processes

If you've cancelled a long-running DML operation such as an UPDATE or DELETE and it is taking a while to come back to the prompt, most likely it is rolling back its changes. (You may also see this for sessions that have been killed but are still listed in v$session.) Querying v$transaction a couple of times will conform this, as you will see the number of undo blocks in use by the session going down, and when it reaches zero the rollback will be complete.

The idea behind undo_tracker.sql is to calculate the rate at which the undo block count is going down based on the amount of change and the time elapsed, and extrapolate how long it will take to complete at the current rate.

Rather than relying on DBMS_LOCK.SLEEP to pause between the two samples (inconveniently, the only 'sleep' commands in Oracle are in privileged system packages to which standard users do not have access), undo_tracker.sql pauses and prompts you to press Return. This means the only privilege needed to run it is SELECT_CATALOG_ROLE or equivalent.

col undo_records    new_value undo_records_start
col time_now        new_value time_start
col session_id      new_value session_id
col session_serial  new_value session_serial
 
col 1 new_value 1
select 1 from dual where 1=2;
def session_specifier = '&1'
 
select dbms_utility.get_time as time_now
     , se.sid         as session_id
     , se.serial#     as session_serial
     , se.username
     , se.status      as session_status
     , tr.status      as transaction_status
     , tr.start_time  as transaction_start
     --, tr.name        as transaction_name
     --, tr.used_ublk   as undo_blocks
     , tr.used_urec   as undo_records
from   v$session      se
     , v$transaction  tr
     , v$rollname     rn
     , v$rollstat     rs
where  se.type = 'USER'
and    (   se.sid = nvl(to_number('&session_specifier'),se.sid) -- and    se.status = 'KILLED'
        or se.username = nvl('&session_specifier',se.username) )
and    tr.addr = se.taddr
and    rn.usn = tr.xidusn
and    rs.usn = rn.usn
order by sid;
 
pause >>> wait a few seconds then press Enter...
 
col undo_records  new_value undo_records_end
col time_now      new_value time_end
 
select se.sid         as session_id
     , se.username
     -- ,se.status      as session_status
     -- , tr.status      as transaction_status
     , tr.start_time  as transaction_start
     --, tr.name        as transaction_name
     --, tr.used_ublk   as undo_blocks
     , tr.used_urec   as undo_records
     , round((dbms_utility.get_time - to_number('&time_start'))/100,1) sample_interval
     , to_number('&undo_records_start') - tr.used_urec as undo_rec_change
     , round(100 * abs(to_number('&undo_records_start') - tr.used_urec) / (dbms_utility.get_time - to_number('&time_start')),1) undo_rec_change_per_sec
     , round(tr.used_urec / (100 * abs(to_number('&undo_records_start') - tr.used_urec) / (dbms_utility.get_time - to_number('&time_start'))) / 60,1) minutes_to_undo
     , round(tr.used_urec / (100 * abs(to_number('&undo_records_start') - tr.used_urec) / (dbms_utility.get_time - to_number('&time_start'))) / 3600,1) hours_to_undo
from   v$session      se
     , v$transaction  tr
     , v$rollname     rn
     , v$rollstat     rs
where  se.sid = to_number('&session_id')
and    se.serial# = to_number('&session_serial')
and    tr.addr = se.taddr
and    rn.usn = tr.xidusn
and    rs.usn = rn.usn
order by sid;
 

Source code: http://www.williamrobertson.net/code/undo_tracker.sql