Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Automatic Segment Space Management

Automatic free space management is only available in locally managed tablespaces. It removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment. The bitmap is stored in separate blocks known as bitmapped blocks (BMBS). This relieves the contention on the segment header that occurs with freelists.

Prior to Oracle 9i, each block had to be read so the freelist could be checked to see if there was room in the block. In 9i, the bitmap can be checked reducing the number of blocks read unneccessarily. The bitmap is constantly kept up to date with changes to the block making freespace management easier and reducing wasted space as blocks can be kept fuller since the overhead of freelist processing has been reduced.

The BMBs contain Root, Branch and Leaf blocks. This organization allows index-like searches for free blocks. Since free blocks are no longer stored in a list it cannot be guaranteed that contiguous blocks will be used within the segment. For this reason the High Water Mark has been split in two:

The LHWM and the HHWM may not be the same value depending on how the bitmap tree was traversed. If different the blocks between them may or maynot be formated for use. The HHWM is neccessary so that direct load operation can guarantee contiguous unformated blocks.

For automatic free space management to occur objects must be placed in a tablespace created with the SEGMENT SPACE MANAGEMENT AUTO clause. Any object within this tablespace will take advantage of this feature.

CREATE TABLESPACE tsh_1
DATAFILE 'C:\Oracle\Oradata\TSH1\tsh1.dbf'
SIZE 10M EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

The PCTUSED, FREELIST and FREELIST GROUPS parameters are ignored in this type of tablespace.

The SEGMENT_SPACE_MANAGEMENT column in the DBA_TABLESPACES and USER_TABLESPACES views indicates the type of segment management used within each tablespace, AUTO or MANUAL.

To upgrade an existing object to use automatic freespace management simply create a new tablespace and use the ALTER...MOVE syntax to place the object within the new tablespace. To downgrade the database to a previous version all tablespaces with automatic free space management must be dropped.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.