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.

The mechanism Oracle actually uses to track the free space in an extent is the freelist.
Each table has one or more freelists, which contain a list of all free data blocks in the current extent.
If a data block is filled to the maximum space allowed, it is removed from the freelist. If the space used by a data block drops below the percentage specified in the PCTUSED parameter, the block is placed back onto the freelist. You can specify more than one freelist for a table. Because Oracle must check the freelist for each insert into a table, you can have multiple freelists if your usage scenario calls for frequent simultaneous inserts. Multiple freelists can avoid any potential contention for the freelist contents.

• Coalescing Indexes to Reduce Fragmentation
  The coalesce command tells the database to merge the contents of the index blocks to free blocks for reuse later, where it is possible to do so.
  Here is an example:
  

  SQL> alter index test_idx1 coalesce;
  Index altered.
  SQL>
  

  
  Coalescing an index does not release space back to the database. The purpose of the coalesce command is to reduce fragmentation in an index. It does not deallocate space that has been allocated to an index segment. Coalescing an index performs an in-place reorganization of the index data. It combines adjacent leaf blocks into a single leaf block and puts the newly empty leaf blocks on the free list of the index segment. The freed up index leaf blocks are reused by the database during subsequent block splits.
  The goal here is to reduce the free space within the leaf blocks of an index. The database scans the index leaf blocks to compare the free space in neighboring index blocks. If there is free space in a block, the contents of the block are merged with the contents of another block, thus freeing up index leaf blocks where possible. The database removes any freed index blocks from the index structure and places them on the free list of index blocks.
  Coalescing an index keeps the space you allocated for the index intact; it does not return the unused space to the database. If you have a case where you are dealing with an index with monotonically increasing values such as on a sequence or a date and you delete a lot of the old values, coalescing might be helpful. Many shops regularly purge older data based on the sequence number or a data range. Coalescing indexes in such cases helps you reclaim the unused space, which is not going to be reused by the indexes anyway. If you are performing a select of all the rows in a table with such an index and are ordering the results by the indexed column, the database will have to read the mostly empty index leaf blocks. Queries might perform better when you coalesce such an index. Coalescing rather than rebuilding the index is the right action to take here. Unlike in the case of an index rebuild, coalescing an index does not require additional disk space; rebuilding an index requires space both for the original and the new index structures until the index is rebuilt. Coalesce also runs much faster than an index rebuildin most cases, freeing up unused leaf blocks for reuse.
  Oracle tracks the data blocks with available space by using 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 following series of images below.