I had a situation where I had to killed the long running session in my Oracle database.Session does not terminate immediately. It is marked for 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,