The PCTINCREASE parameter in the STORAGE clause determines the degree to which Oracle will automatically increase the size of subsequent extent allocations. This value is expressed as an integer percentage, and is applied to the then-current value of the NEXT parameter. For example, if an object is created with INITIAL 81920, NEXT 81920, and PCTINCREASE 10, this means that after each new extent is allocated, Oracle will dynamically increase the value of the NEXT parameter for that object by 10%. Table 11.1 illustrates the situation in a database with a 2048-byte blocksize.
Extent |
Size in Bytes |
Size in Blocks |
Comment |
Initial extent |
81920 |
40 blocks |
Uses INITIAL parameter |
2nd extent |
81920 |
40 blocks |
Uses specified NEXT value |
3rd extent |
90112 |
40 blocks |
NEXT increased by 10% |
4th extent |
100352 |
49 blocks |
NEXT increased by another 10% and rounded to next block |
If the default PCTINCREASE for a tablespace is set to 0, Oracle will not automatically coalesce smaller but contiguous extents back into larger extents in that tablespace. As a result, Oracle may be unable to allocate a particular extent even when sufficient contiguous blocks are available. Setting PCTINCREASE to a value of 1 will overcome this problem.