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”,
sl.start_time,
sl.elapsed_seconds,
sl.time_remaining,
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)
ORDER BY s.SID;
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.