How to monitor transaction Rollback Progress

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,