|Lesson 9||Oracle Free space |
|Objective||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 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 dont 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?
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
PCTUSED parameters is to step through the SlideShow below.
- For this block, the PCTFREE has been set to 20 and the PCTUSED has been set to 40.
- As rows are added to the block, the block fills up.
- If a new row would leave the block 80% filled, Oracle stops considering the block as a destination for new rows.
- Subsequent deletions take the overall space utilization below 40%
- 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.