How to monitor transaction Rollback Progress

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