Tales From A Lazy Fat DBA

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.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
—————————— ———- ———
SYSTEM                         DICTIONARY USER
SYS_UNDOTS                     LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in “dictionary managed” mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE etr1 DATAFILE ‘/oradata/etr1_01.dbf’ SIZE 80M
EXTENT MANAGEMENT DICTIONARY
Using LMT, each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files.

SQL> CREATE TABLESPACE etr2 DATAFILE ‘/oradata/etr2_01.dbf’ SIZE 80M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Advantages in using Locally Managed Tablespaces Over Dictionary Managed Tablespaces

• Because locally managed tablespaces do not record free space in data dictionary, it reduces contention on these tables.
• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
• Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
• Reduced fragmentation