Scribd
Upload
29 views2 pages

LMT vs DMT: Extent Management Explained

This document compares dictionary managed tablespaces (DMT) and locally managed tablespaces (LMT) in Oracle databases. LMTs manage free space using block bitmaps in datafiles rather than dictionary tables, avoiding contention issues. Converting between DMTs and LMTs can be done using PL/SQL procedures. LMTs offer advantages like reduced recursive space management and rollback overhead compared to DMTs.

Uploaded by

Rose Ma
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
29 views2 pages

LMT vs DMT: Extent Management Explained

This document compares dictionary managed tablespaces (DMT) and locally managed tablespaces (LMT) in Oracle databases. LMTs manage free space using block bitmaps in datafiles rather than dictionary tables, avoiding contention issues. Converting between DMTs and LMTs can be done using PL/SQL procedures. LMTs offer advantages like reduced recursive space management and rollback overhead compared to DMTs.

Uploaded by

Rose Ma
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd

Difference between Dictionary managed tablespace (DMT)

and Locally managed tablespace (LMT)

Locally Managed Tablespaces:A tablespace that manages its own extents


maintains 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 group of
blocks. When an extent is allocated or freed for reuse, Oracle 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 (like
[Link]$, [Link]$) in the data dictionary (except for special cases such as
tablespace quota information).When you create a locally managed
tablespace, header bitmaps are created for each datafile. If more datafiles
are added, new header bitmaps are created for each added [Link]
management of extents automatically tracks adjacent free space,
eliminating the need to coalesce free extents. The 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.

Dictionary Managed Tablespaces:In DMT, to keep track of the free or used


status of blocks, oracle uses data dictionry tables. When an extent is
allocated or freed for reuse, free space is recorded in the [Link]$ table,
and used space in the [Link]$ table. Whenever space is required in one of
these tablespaces, the ST (space transaction) enqueue latch must be
obtained to do inserts and deletes agianst these tables. As only one process
can acquire the ST enque at a given time, this often lead to contention.
These changes generate rollback information because they update tables
(like [Link]$, [Link]$) in the data dictionary.

Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed


Tablespaces(DMT):

1. Reduced recursive space management2. Reduced contention on data


dictionary tables3. No rollback generated4. No coalescing required
Converting DMT to LMT:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local(‘ts1′);PL/SQL


procedure successfully completed.

Converting LMT to DMT:

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local(‘ts2′);


PL/SQL procedure successfully completed.

Important Points:1. LMTs can be created asa) AUTOALLOCATE: specifies that


the tablespace is system managed. Users cannot specify an extent size.b)
UNIFORM: specifies that the tablespace is managed with uniform extents of
SIZE bytes. The default SIZE is 1 megabyte.2. One cannot create a locally
managed SYSTEM tablespace in 8i.3. This is possible with in 9.2.0.X, where
SYSTEM tablespace is created by DBCA as locally managed by default. With a
locally managed SYSTEM tablespace, the rest of the tablespaces in such
database have to be locally managed as well.4. Locally managed temporary
tablespaces can not be of type “permanent”.

You might also like