Oracle Database Administration: The Essential Refe

Storage Allocation Parameters

As you can see from the formulas presented above, the utilization of space within an Oracle block is complex. One major factor in the use of this space is the value set for PCTFREE. This parameter, which is normally set when an object is created but can be modified later, controls how much empty space is reserved in each block when a row is inserted. PCTFREE is specified as a percentage of the data storage area of a block reserved for future updates to rows in that block.

For example, a PCTFREE value of 10 would reserve 10% of the space remaining in a block after allocation of block headers for updates (note that this is not 10% of the total blocksize). This space is used by Oracle to allow rows stored in the block to grow in size. If there is not enough space left in a block to contain all of the data for an updated row, Oracle must allocate an additional block to hold the overflow. This is called row chaining , and should be avoided, since multiple, non-sequential I/Os will be required to read a single row of data. Of course, it is possible to have a single row, or even a single column, that is larger than the available space in a block. In this situation, block chaining is inevitable, although the DBA might consider a larger DB_BLOCK_SIZE, if warranted.