Toad World® Forums

Rollback remaining time?

Let’s say we have a session that is ACTIVE and it is rolling back 10 million rows.

What SQL query would indicate the remaining time needed to complete this roll back?

I don’t know of any “time remaining” query, but you can do this query repeatedly to watch the progress

SELECT t.USED_UBLK, s.username, s.machine, s.program
FROM V$TRANSACTION t, v$session s
where t.addr = s.taddr;

The first column’s value will approach zero as the rollback progresses. The row will disappear when the transaction is complete.

Oh, you might be able to find the rollback in v$Session_longops. You could use this query:

Select decode(sl.totalwork, 0, 0, round(100 * sl.sofar/sl.totalwork, 2)) “Percent”,
sl.message “Message”,
s.username, s.machine, s.program
from v$Session_longops sl, v$session s
where sl.sid = s.sid
and sl.serial# = s.serial#
and (sl.totalwork > sl.sofar)

To see this in Toad, you can go to the Session Browser, find the session you are interested in, and then click the “Long Ops” tab. I don’t know that all rollbacks appear here but I just did a quick test and it did.