Comparing locally managed and dictionary 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.