Suppose a third transaction wants to change a unlocked row in a block with only two ITLs. The third transaction's server process will try to dynamically create an additional ITL. However, the server process must first ensure the maximum number of ITLs (max_trans) will not be exceeded and also that there is free space available in the block. If the server process cannot create an additional ITL, it will post a TX enqueue wait, and the process will patiently wait. To reduce the likelihood of this occurring, both the default value and the maximum number of ITLs a single block can contain are set to 255. While the value cannot be exceeded, it can be reduced by issuing a simple alter table command.11

Once an ITL has been created in a data block, pushing deeper into the block's free space, the only way to get the space back is to re-create the entire table. Altering the space parameters will not affect ITLs already created. This is why the default number of ITLs is set to 1 (again, two are actually created) and the maximum it set to 255. If the block's concurrency requires more than a few ITLs, Oracle would rather the space be consumed than stall a transaction while posting the TX enqueue wait event.

At first glance, the maximum number of 255 ITLs may seem very limited, but consider this: Think of highest concurrency table, in the highest concurrency application, in the highest concurrency database system you administer. Perhaps there is a table that could possibly have 250 concurrent processes updating, deleting, and inserting rows. Now ask yourself how many processes will be concurrently updating, deleting, or inserting rows into a single block-not the entire table or extent, but in a single block. This is the number of ITLs that may be eventually created in a single block. Even with the highest concurrency applications, to have more than 255 concurrent transactions active in a single block is extremely unlikely. So a maximum of 255 ITLs is really not that limiting after all. However, if this does present a problem, you can reduce block concurrency by increasing the table's pct_free parameter or perhaps add a fixed-length column to reduce the number of rows that could possibly exist in the block.