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
STATE
VARCHAR2(16)
The state of the transaction may be TO BE RECOVERED, RECOVERED, or RECOVERING
UNDOBLOCKSDONE
NUMBER
The number of undo blocks completed on this transaction
UNDOBLOCKSTOTAL
NUMBER
The total number of undo blocks that need recovery

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

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 * from x$ktuxe where ktuxecfl = ‘DEAD’;

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

SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE=‘TEMPORARY’;

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.

Useful Metalink Documents on UNDO:

461480.1 –         FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)
460481.1 –         Troubleshooting ORA-30036 – Unable To Extend Undo Tablespace

Advertisements

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

%d bloggers like this: