Locally vs. Dictionary Managed Tablespaces

By declaring a tablespace as DICTIONARY managed, you are specifying that extent management for segments in this tablespace will be managed using the following dictionary tables:

sys.fet$	Every extent for a particular segment is represented by a row in the sys.fet$ dictionary table. sys.fet$ is actually clustered in the C_TS# cluster.
sys.uet$	Data dictionary view used to store used extents. sys.uet$ is actually clustered in the C_FILE#_BLOCK# cluster.

Oracle updates the above tables in the data dictionary whenever an extent is allocated, or freed for reuse.

To Create Dictionary Managed Tablespaces you can use the following example:

CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 2
MAXEXTENTS 121
PCTINCREASE 0);

A Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle simply changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information). Some of the benefits of using a locally managed tablespace are:

• Reduced recursive space management.
• Reduced contention on data dictionary tables.
• No rollback generated.
• No coalescing required.

The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.

CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.

CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

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.
• 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.
• 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.
• Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
• Reduced fragmentation .