I had a situation where I had to killed the long-running session in my Oracle database. The session does not terminate immediately. It is marked for the kill and the rollback is done in the background.
SQL> ALTER SYSTEM KILL SESSION ’12,2233'; ALTER SYSTEM KILL SESSION ’12,2233' * ERROR at line 1: ORA-00031: session marked for kill SQL> select username, status from v$session where SID=12; USERNAME STATUS ——————– ——– SYS KILLED
So how do we track, whether background rollback is in progress or not?
We can query the dynamic view v$transaction. Column used_ublk will be updated frequently. The value will keep on decreasing. Once the value becomes 0 or no row is returned from this view rollback is complete and entry will be removed from v$session.
select t.used_ublk,s.sid,s.username from v$transaction t, v$session s where s.taddr = t.addr and s.sid = '12'; ----------- 75875 select t.used_ublk,s.sid,s.username from v$transaction t, v$session s where s.taddr = t.addr and s.sid = '12'; -------- 75812 Based on this value, we can calculate the time of rollback easily. Once the rollback is completed, session will be completely gone
or we can use the below query
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal - undoblocksdone "ToDo", DECODE ( cputime, 0, 'unknown', SYSDATE( ( (undoblockstotal - undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" FROM v$fast_start_transactions;