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.