dictionay_vs_locally_managed_tablespace

dictionay_vs_locally_managed_tablespace

Dictionary Managed tablespace

Prior to oracle 8i, extent management was dictionary managed. In this case oracle uses data dictionary tables to keep track of the free and used blocks. Free space is maintained in sys.fet$ table and used space is maintained in sys.uet$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes against these tables. As only one process can acquire the ST enqueue at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Creating Dictionary Managed tablespace:

CREATE TABLESPACE <tablespace name>

  DATAFILE <location of datafile>

SIZE <size of data file>

EXTENT MANAGEMENT DICTIONARY

  DEFAULT STORAGE (

    INITIAL <size of initial extent>

    NEXT <size of next extent>

    MINEXTENTS <min no of extents>

    MAXEXTENTS <max no extents>

    PCTINCREASE 0);

Locally Managed tablespace

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).

Creating Locally Managed tablespaces:

CREATE TABLESPACE <tablespace name>

DATAFILE <datafile location> SIZE <size of datafile>

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT <AUTO | UNIFORM>;

Note: We cannot specify storage parameters in Locally Managed tablespace.

In Locally Managed tablespace, Segment space management can be auto allocate or uniform

1. Auto Allocate: If the tablespace is expected to contain objects of varying sizes which requires different extent sizes and having many extents, then AUTOALLOCATE is the best choice. If it is not important to you to have a lot of control over space allocation and de-allocation, AUTOALLOCATE presents a simplified way to manage a tablespace. Some space may be wasted but the benefit of having Oracle manage your space most likely outweighs this.
2. Uniform: If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. It ensures that you will never have an unusable amount of space in your tablespace.

1mb is the default size.

Auto-allocate is the default parameter if nothing is specified.

Converting DMTS to LMTS:

Use the TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a dictionary-managed tablespace to a locally managed tablespace. This operation is done online, but space management operations are blocked until the migration has been completed. This means that you can read or modify data while the migration is in progress, but if you are loading a large amount of data that requires the allocation of additional extents, then the operation may be blocked.

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('<tablespace name>');

Converting LMT to DMT:

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('<tablespace name>');

Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

Use the DBMS_SPACE_ADMIN package to migrate the SYSTEM tablespace from dictionary-managed to locally managed. The following statement performs the migration:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

Before performing the migration the following conditions must be met:

• The database has a default temporary tablespace that is not SYSTEM.
• There are no rollback segments in the dictionary-managed tablespace.
• There is at least one online rollback segment in a locally managed tablespace, or if using automatic undo management, an undo tablespace is online.
• All tablespaces other than the tablespace containing the undo space (that is, the tablespace containing the rollback segment or the undo tablespace) are in read-only mode.
• The system is in restricted mode.
• There is a cold backup of the database.

All of these conditions, except for the cold backup, are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure.

Note:

After the SYSTEM tablespace is migrated to locally managed, any dictionary-managed tablespaces in the database cannot be made read/write. If you want to be able to use the dictionary-managed tablespaces in read/write mode, then Oracle recommends that you first migrate these tablespaces to locally managed before migrating the SYSTEM tablespace.

Temporary Tablespace as Locally Managed Tablespace :-

You can also create a temporary tablespace as Locally Managed tablespace as

  CREATE TEMPORARY TABLESPACE <temp tablespace name> TEMPFILE <location of temp datafile>      SIZE <size of tempfile> REUSE

     EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent size>;

Advantages of locally managed tablespace

1. Performance is enhanced since the storage values are placed in bit maps rather than in tables .Every time when oracle has to use an extent it has to check the sys.fet$ table and update sys.fet$ and sys.uet$ tables so this leads to performance degradation
2.  Oracle does not need to manage the tables
3.  Oracle manages the extent size based on transaction size in locally managed tablespace (auto allocate).
4.  Reduced contention on Data Dictionary tables: Because LMTS do not record free space in Data Dictionary.
5. Avoids recursive space management operations, which can occur in DMTS If space consuming or releasing in an extent results in another operation that consumes or releases space in another extents.
6. No rollback generated: Changes in the bitmap will not generate rollback because they do not update tables in Data dictionary.
7. Less Fragmentation due to uniform or oracle determined extent sizes.
8. Less Wasted free space due to uniform or oracle determined extent sizes.
9. Since tables are not managed(sys.fet$ and sys.uet$ as in dictionary managed tablespaces),no need for tables to be in database buffer cache
10. No need to acquire space transition latch

Disadvantage of Locally Managed Tablespace

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.

Differences between locally managed tablespaces and dictionary managed tablespaces

Dictionary Managed tablespace	Locally Managed tablespace
Maintains sys.fet$ and sys.uet$ tables for free and used blocks	Maintains bit maps
Sys.fet$ and sys.uet$ tables need to be cached in buffer cache	No need to cache the bit maps
For space requirements ,Space Transition latch must be obtained	No need to obtain any latch to use bit maps
Performance degrades	Performance enhances
Storage is specified using the parameters initial ,next ,min extents ,max extents and pct increase	Auto Allocate: Depending on size of transaction oracle allocates the extents Uniform: All the extents are of same size as specified
All the tablespaces can be dictionary managed	If system tablespace is locally managed, then all other tablespaces should be locally managed tablespace.