Tracing a lost transaction which is rolling back

If a long running transaction (indeed any transaction) is cancelled or fails, then Oracle needs to cleanup the uncommitted work that was done by this transaction so that other transactions can progress. This cleanup involves rolling back the uncommitted work.

You can investigate/evaluate how long SMON will run to recover transaction using V$FAST_START_TRANSACTIONS view which contains information about the progress of the transactions that Oracle is recovering.

Useful columns from v$fast_start_transactions are:
Column Datatype Description
The state of the transaction may be TO BE RECOVERED, RECOVERED, or RECOVERING

The number of undo blocks completed on this transaction

The total number of undo blocks that need recovery

Also, to check if the transaction that needs rollback is still present.

select * from x$ktuxe where ktuxecfl = ‘DEAD’;

To determine the number of undo blocks required for rollback you can use the select statement below:

select distinct ktuxesiz from x$ktuxe;

To check whether there is a large number of temporary extents that migh being cleaned up run the following query a few times:


FYI: Here is a simplified description of how AUM (Automatic Undo Management) works, and how the undo extents (in the UNDO tablespace) are being allocated to active transactions:
– When a new active transaction comes in, Oracle allocate new extents if there is free space available in the undo tablespace.
– The undo tablespace does *not* shrink nor does it return space back to the OS (no other tablespace does either). It internally changes the status of its extents something like from "used" to "unexpired" to "expired" to "free" – over time, depending on the status of the transaction (commited/uncommited) and the undo_retention time (expired or not).
– If autoextend is turned on, Oracle allocates new extents, thus the datafile grows as a result of that.
– Even after the transactions are completed those undo extents will remain in EXPIRED status and are *not* automatically deallocated as they will be reused when necessary. Once allocated you cannot deallocate them. Also, one cannot shrink the undo datafiles as for normal tablespaces. As the blocks are being used by undo extents (expired or not), oracle will not allow you to resize.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: