watching killed transaction undo

once a session which is doing a transaction gets killed before the session commits or rollbacks oracle needs to recover(undo) the work done by this session. one way to watch the progress of this recovery is to use the v$fast_start_servers and v$fast_start_transactions views.

small test case:

-- FIRST SESSION
drop user u1 cascade;
create user u1 identified by u1
  default tablespace users
  temporary tablespace temp
  quota unlimited on users;
grant create session, create table to u1;
grant select on dba_objects to u1;
connect u1/u1
create table t1 as select * from dba_objects;
insert into t1 select * from t1;
/
/
/
commit;

in a second session get the sid and serial# for the first session:

connect / as sysdba
select sid,serial# from v$session where username = 'U1';

back in the first session do some (stupid) work on the table:

begin
  while 1=1 loop
    update t1 set OBJECT_NAME = OBJECT_NAME;
  end loop;
end;
/

this will write undo data without committing the work. now, in the first session, kill the session which is doing the transaction:

alter system kill session '[SID_FROM_ABOVE],[SERIAL#_FROM_ABOVE]';

if your hardware is slow enough or you are fast enough or you keep the loop running for some time before killing the session you can now see the slave(s) doing recovery:

select * from v$fast_start_servers;

STATE	    UNDOBLOCKSDONE	  PID XID
----------- -------------- ---------- ----------------
RECOVERING	      9379	   26 020021006D010000

… and the transaction(s) being recovered:

select * from v$fast_start_transactions;

       USN	  SLT	     SEQ STATE		  UNDOBLOCKSDONE UNDOBLOCKSTOTAL	PID    CPUTIME	PARENTUSN  PARENTSLT  PARENTSEQ XID
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ----------------
PXID		 RCVSERVERS
---------------- ----------
	 2	   33	     365 RECOVERED		   19357	   19357		    11					020021006D010000
			  1

	 5	    4	     357 RECOVERED		   17220	   17220		    13					0500040065010000