8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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:
- Low High Water Mark (LHWM) - Like the old High Water Mark, all blocks below this point have already been formated for use.
- High High Water Mark (HHWM) - Indicates the point above which no blocks have been formatted.
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...