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.


   

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