Problem solve Get help with specific problems with your technologies, process and projects.

Monitoring rollback progress

This statement will help you determine how long a rollback will take and how much of it is completed.

When a large transaction takes a long time to rollback, it is good to know how much of the rollback is done and estimate how long it is going to take. Given the session's sid, it can be done with the simple statement below, tested on Oracle9i. When a transaction is rolling back, the t.used_ublk and t.used_urec will decrease until they become 0. By sampling the two measures at different points of time, you can calculate how fast the rollback is and when it is going to complete.

SELECT t.used_ublk, t.used_urec  
FROM v$session s, v$transaction t
WHERE s.taddr=t.addr
and s.SID =:sid;


 

This was last published in September 2005

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close