Comparing locally managed and dictionary managed tablespaces

What is the difference between a locally managed and a dictionary managed tablespace, and what are the benefits of using a locally managed tablespace?

What is the difference between locally managed and dictionary managed tablespaces, and what are the benefits of using a locally managed tablespace?

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks called an extent is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary or in the tablespace itself. Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:

  • Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
  • Local management of extents tracks adjacent free space, eliminating coalescing free extents.
  • Reliance on data dictionary is reduced. This minimizes access to the data dictionary, potentially, improving performance and availability.

Due to these improvements, Oracle recommends using locally managed tablespaces for all new tablespaces if fragmentation is expected to be an issue The only drawback with locally managed tablespaces is that 'used-extent' information is not kept in the data dictionary. It must be read from the segment header blocks (and additional extent map blocks if any) whenever it is required, including queries against DBA_SEGMENTS and DBA_EXTENTS. If a tablespaces with a large number of mostly small segments is locally managed rather than dictionary managed, then access to these views can cause a lot more physical I/O and thus impact the cache retention of user data. Furthermore, if the segments are mostly constant in size then the risk of the tablespace contributing to ST enqueue contention if dictionary managed is low. Thus, there is little motivation to make such a tablespace locally managed and a minor performance risk in doing so.

Dig Deeper on SAP development and programming languages

Data Management
Business Analytics
Content Management