How to monitor transaction Rollback Progress

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;