How does Oracle uses free space within data blocks?
Oracle Free Space Data Blocks
The data block is the lowest level of granularity in Oracle storage,
but you can set storage parameters that condition exactly how Oracle uses the space within those data blocks.
When you update a row in an Oracle database,
Oracle tries to update the values for the row in the same data block. If you have a situation where you will be updating rows and increasing their size, you might want to initially leave some empty space in the row so that Oracle will not have to rewrite the larger row to another location.
You can reserve empty space with the PCTFREE parameter. PCTFREE specifies the percentage of a data block reserved for future updates. If you set a PCTFREE of 20 when Oracle went to insert a new row into the block, the block would only be filled to 80% of its capacity. If the insert required that the block be filled to a greater percentage, Oracle would move on to the next data block for the insert.
The default value for PCTFREE is 10, so if you don’t specify a PCTFREE for a database object, Oracle will use data blocks for inserts up to 90% of their capacity.
Oracle tracks the data blocks with available space by using freelists. Click the Audio button to hear more about freelists.
As soon as the PCTFREE threshold is reached, Oracle stops considering a data block for further insertions. What if data is deleted, freeing space?
The PCTUSED parameter tells Oracle to begin inserting rows into a block if the overall space usage within the
block drops to a certain level. If you set the PCTUSED parameter to 40, as soon as deletions left a block with only 39% of its space used, Oracle would begin inserting rows into the block again.
The best way to understand the interaction between the PCTFREE and PCTUSED parameters is to step through the SlideShow below.
Oracle Space Blocks
The next lesson demonstrates how to check for these storage parameters in the data dictionary.