Free Space Management In Data Segments of Oracle Database


Free space can be managed automatically inside oracle database segments. You specify automatic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. Automatic segment-space management offers the following benefits:

  • Ease of use
  • Efficient utilization of space, mainly for the objects with highly varying row sizes
  • Better run-time adjustment to variations in concurrent access
  • Better multi-instance behavior in terms of performance/space utilization
Space can be released by issues DELETE statements or UPDATE statement. UPDATE statement can only release free space, when the updated values are smaller than the origanl values.
The released space from the above stament can be utilized when an INSERT statement is issued and this can be under following circumstances.
  • If the INSERT statement is issued by the same user after the statement that released free space, then same space can be utilized by the INSERT statement.
  • If the INSERT statement is isssued by some other user for a separate transaction, then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.

Released space may or may not be contiguous with the main area of free space in a data block. Oracle coalesces the free space of a data block only when
(1) an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, and
(2) the free space is fragmented so the row piece cannot be inserted in a contiguous section of the block. Oracle does this compression only in such situations, because otherwise the performance of a database system decreases due to the continuous compression of the free space in data blocks.