|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.
Oracle Space Blocks
The next lesson demonstrates how to check for these storage parameters in the data dictionary.