INITRANS specifies number of update transaction entries - Oracle Schema

Discussion

• Nirja Shah   -Posted on 25 Sep 15
  - INITRANS specifies the number of update transaction entries for which space is initially reserved in the data block header.
  
  - Space is reserved in the headers of all data blocks in the associated segment.
  
  - As multiple transactions concurrently access the rows of the same data block, space is allocated for each update transaction entry in the block.
  
  - Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of the free space in a block, if available.
  
  - Once allocated, this space effectively becomes a permanent part of the block header.
  
  - The database ignores MAXTRANS when specified by users only for new objects created when the COMPATIBLE initialization parameter is set to 10.0 or greater.
  
  - You should consider the following when setting the INITRANS parameter for a schema object:
  
  1. The space you would like to reserve for transaction entries compared to the space you would reserve for database data
  
  2. The number of concurrent transactions that are likely to touch the same data blocks at any given time
  
  - For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access to the same data block is low.
  
  - Therefore, INITRANS can be set low, especially if space is at a premium in the database.
  
  - Alternatively, assume that a table is usually accessed by many users at the same time.
  
  - In this case, you might consider preallocating transaction entry space by using a high INITRANS.

➨ Post your comment / Share knowledge