Transactions and Consistency

Transaction table:

• There is a limited number of entries in transaction table, so you will have to keep reusing them. Each time you reuse an entry in the table, you increment
  the wrap# for that entry.
• “Does the wrap# get reset every time the instance restarts?” The answer is no.
• Check the transaction table information: x$ktuxe(query against the structure will actually cause Oracle to visit each undo segment header block of each
  undo segment in the database)
  

Column	Description
index	Identifies the row in the transaction table and is used as part of the transaction id. This is known most commonly as the transaction table slot number. (It’s not a value that’s physically stored in the block, by the way—it’s a value derived by position when we dump the block.)
state	The state of the entry: 9 is INACTIVE, and 10 is ACTIVE.
cflags	Bit flag showing the state of a transaction using the slot: 0x0 no transaction, 0x10 transaction is dead, 0x80 active transaction. (0x90 – dead and being rolled back).
wrap#	A counter for the number of times the slot has been used. Part of the transaction id.
uel	A pointer to the next transaction table slot to use after this one goes active. In a new segment this will look very tidy, but as transactions come and go, the pointers will eventually turn into a fairly random linked list wandering through the slots.
scn	The commit SCN for a committed transaction. (Since a rollback call ends with a commit, this would also be used for the commit SCN at the end of a rollback). For most versions of Oracle, this column is also used as the start SCN when the transaction is active, but, strangely, my copy of 10.2.0.3 dumps this as zero for active transactions.
dba	Data Block Address of the last undo block that the transaction used to write an undo record. This allows Oracle (particularly on crash recovery) to find the last undo record generated by a transaction so that it knows where to start the process of rolling back.
nub	Number of undo blocks used by this transaction so far. (During a transaction rollback you can watch this number decrease.)
cmt	Commit time to the nearest second, measured as the number of seconds since midnight (UTC) of 1 January 1970. It is zero when the transaction is active. Since this seems to be a 32-bit number it has crossed my mind to wonder whether some systems may run into trouble in January 2038 if it’s treated as a signed integer or in February 2106 if it’s treated as unsigned.

Transaction ID:

• 0x0009.002.00002013: the undo segment number, the index number of the entry in the transaction table, and the latest wrap# of that entry
• Check which undo segment this is and the location of the header block: query view dba_rollback_segs by segment_id.
• Query transaction id: v$transaction and v$lock

Reviewing the Undo Block

• A single undo block may contain undo records from multiple transactions.
• Active transactions will not write to the same undo block at the same time, but several transactions may have used the same undo block one after the other.(free block pool)

Data Block Visits and Undo

• ITL(The interested transaction list): exists to identify transactions that recently changed a data block
  

Column	Description
Itl	The array index for the list. The number isn’t physically stored in the block; it’s generated by the code that does the dump. This value is used in the lock byte (lb:) for a row to show which transaction has locked the row.
Xid	The transaction id of a recent transaction that has modified this block. The format is undo segment . undo slot . undo sequence number.
Uba	The undo record address—including the sequence (or incarnation) number—of the block of the most recent undo record generated by this transaction for this block. The format is Absolute block address . block sequence number . record within block. (The “b” in the label suggests byte or block, but neither of those interpretations is quite accurate.)
Flag	Bit flag identifying the apparent state of this transaction: ----: active (or “never existed” if every field in the Xid is zero). --U-: Upper bound commit (also set during “fast commit”). C---: Committed and cleaned out (all associated lock bytes have been reset to zero). -B--: May be relevant to the recursive transactions for index block splits. I have seen comments that this flag means the UBA will point to a record holding the previous content of the ITL entry, but I have not managed to confirm this. ---T: I have seen comments that this means the transaction was active during block cleanout, but I have not managed to confirm this.
Lck	Number of rows locked by this transaction in this block.
Scn/Fsc	Depending on the Flag, the commit SCN or the number of bytes of free space that would become available if this transaction committed (Free Space Credit).

Parameters:

• initrans --- create an object with a larger ITL in each block 

             maxtrans --- limit the size of the ITL

• • For an index: the default value is 2(one for branch and another for leaf), even though the data dictionary will still report 1
  • And if you load some blocks using direct path loads, you will find that they initially have three ITL entries.
  • on an index leaf block split, the old ITL is copied forward into the new leaf block
• Concurrent Action
• LOBs
  • Oracle has some special methods for handling undo and redo on LOBs