watching killed transaction undo

August 22, 2014 — Leave a comment

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
Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s