Space Management   «Prev  Next»

Lesson 9Oracle Free Space
ObjectiveHow 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.

Specifying PCTFREE

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.

Freespace
Oracle tracks the data blocks with available space by using freelists. Click the Audio button to hear more about freelists.

Specifying PCTUSED

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.

PCTFREE

1) For this block, the PCTFREE has been set to 20 and the PCTUSED has been set to 40. This means that each block will reserve 20% of their space, filling 80% initially, and start to reuse blocks when they shrink to only being 40% full.
1) For this block, the PCTFREE has been set to 20 and the PCTUSED has been set to 40. This means that each block will reserve 20% of their space, filling 80% initially, and start to reuse blocks when they shrink to only being 40% full.

2) As rows are added to the block, the block fills up.
2) As rows are added to the block, the block fills up.

3) If a new row would leave the block 80% filled, Oracle stops considering the block as a destination for new rows.
3) If a new row would leave the block 80% filled, Oracle stops considering the block as a destination for new rows.

4) Subsequent deletions take the overall space utilization below 40%
4) Subsequent deletions take the overall space utilization below 40%

5) Once this happens, Oracle starts inserting new rows into the data block again until the 80% threshold is reached, which restarts the cycle.
5) Once this happens, Oracle starts inserting new rows into the data block again until the 80% threshold is reached, which restarts the cycle.

Oracle Space Blocks
The next lesson demonstrates how to check for these storage parameters in the data dictionary.