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)
  

ColumnDescriptionindexIdentifies 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.)stateThe state of the entry: 9 is INACTIVE, and 10 is ACTIVE.cflagsBit 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.uelA 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.scnThe 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.dbaData 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.nubNumber of undo blocks used by this transaction so far. (During a transaction rollback you
can watch this number decrease.)cmtCommit 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
  

ColumnDescriptionItlThe 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.XidThe transaction id of a recent transaction that has modified this block. The format is undo
segment . undo slot . undo sequence number.UbaThe 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.)FlagBit 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.LckNumber of rows locked by this transaction in this block.Scn/FscDepending 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