Oracle Database Administration: The Essential Refe

Data storage area

The balance of the block is used for data storage—for example, to store the actual rows of a table. The calculation of the available storage in each block is not straightforward, since it is dependent on several factors, including:

• Oracle blocksize (DB_BLOCK_SIZE)

• Percent free space (PCTFREE)

• Average row length

• Number of rows stored per block

The average row length can be estimated as:

3 bytes row header

+ 1 byte per non-LONG column

+ 3 bytes per LONG column

+ average length of all table columns

The header space per row can be calculated as:

3 + (number of non-LONG columns) +

3 × (number of LONG columns)

The number of rows per block can then be calculated as:

((blocksize - (57 + 23 × INITRANS))

- (blocksize - (57 + 23 × INITRANS))

× (PCTFREE/100) - 4 - 2 × rows per block)

/ (average row length + header space per row)

Finally, the available space in the block can be calculated as:

(blocksize - (57 + 23 × INITRANS))

- ((blocksize - (57 + 23 × INITRANS)) × PCTFREE × 100)

- 4 - 2 × rows per block

Even this description simplifies the calculations a bit, since we do not take into account the possibility of trailing NULLs, LONG strings, and so on, which may have an impact on the exact calculation.