===================================================================
STEP-I First, calculate the total block header size:
===================================================================
block header = fixed header + variable transaction header + table directory + row directory
where:
fixed header = 57 bytes (the 4 bytes at the end of the block have already been taken account of in the 24 bytes for the cache header)
variable transaction header = 23 * i where i is the value of INITRANS for the table, or can grow dynamically up to MAXTRANS.
table directory = 4 * n where n is the number of tables. n = 1 for non-clustered tables.
row directory = 2 * x where x is the number of rows in the block.
Using the above formula, the initial block header size for a non-clustered table with INITRANS = 1 is:
block header = 57 + 23 + 4 + 2x = (84 + 2x) bytes
The space reserved for data within the block, as specified by PCTFREE, is calculated as a percentage of the block size minus the block header.
available data space = (block size – total block header) – ((block size – total block header) * (PCTFREE/100))
For example, with PCTFREE = 10 and a block size of 2048, the total space for new data in a block is:
available data space = (2048 – (84 + 2x)) – ((2048 – (84 + 2x)) *(10/100))
= (1964 – 2x) – ((2048 – 84 – 2x) * (10/100))
= (1964 – 2x) – (1964 – 2x) * 0.1
= (1964 – 2x – 196 + 0.2x) bytes
= (1768 – 1.8x) bytes
==================================================================================
Step II Now, calculate the combined data space required for an average row.
==================================================================================
Calculating this depends on the following:
1. The number of columns in the table definition.
2. The datatypes used for each column.
3. The average value size for variable length columns.
A test database similar to the production database will be helpful here. To calculate the combined data space for an average row in a table, use the following query:
SELECT AVG(NVL(VSIZE(col1), 1)) +
AVG(NVL(VSIZE(col2), 1)) +
… +
AVG(NVL(VSIZE(coln), 1)) “SPACE OF AVERAGE ROW”
FROM table_name;
col1, col2, … , coln are the column names of the table and table_name is the table being evaluated.