Tuesday, March 10, 2009

Oracle:How to calculate Rollback duration

Here is a query to figure out how long it will take for a rollback to complete.

Select username,opname,start_time,last_update_time,totalwork,units,sofar,
elapsed_seconds,time_remaining,sql_Text
from v$session_longops l, v$sql s
where l.sql_id = s.sql_id(+)
and sid = sid_of_the_session_doing_rollback

Above query takes SID as input parameter.Replace "sid_of_the_session_doing_rollback" with the SID

For a Rollback Operation

Opname will say "Transaction Rollback"
Total Work: Amount of work.
Units: Total work is mesaured in terms of UNITS columns
SoFar: Work done so far.
SQL_TEXT: Sql that is being rolled back.

Since this sql queries LONGOPS View, you can use it to get an idea for how long any process would take.

No comments:

Post a Comment