DMT

From Oracle FAQ
Jump to: navigation, search

DMT (Dictionary Managed Tablespace) is a type of tablespace where extents are managed using data dictionary tables (sys.uet$ and sys.fet$) instead of the tablespace's header.

History[edit]

DMT tablespaces are available since forever (most likely since Oracle 2).

Check if a tablespace is dictionary managed[edit]

To see if a tablespaces is defined as dictionary managed, run:

SQL> SELECT tablespace_name, extent_management FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         DICTIONARY
SYSAUX                         LOCAL
TEMP                           LOCAL
UNDOTBS02                      LOCAL
RADIUS_DATA                    LOCAL
RADUIS_INDEX                   LOCAL
UNDOTBS03                      LOCAL
STATSPACK                      LOCAL
UNDOTBS1                       LOCAL

9 rows selected.

Coalescing of free space[edit]

SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time.

SMON will not coalesce free space if a tablespace's default storage parameter PCTINCREASE is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the command:

ALTER TABLESPACE ... COALESCE;

For releases older than 7.3, use:

SQL> alter session set events 'immediate trace name coalesce level n';

where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;

You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.

Converting between DMT and LMT[edit]

From DMT to LMT:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
PL/SQL procedure successfully completed.

From LMT to DMT (only possible if SYSTEM tablespace is a DMT):

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');
PL/SQL procedure successfully completed.

Also see[edit]

  • LMT - Locally Managed Tablespaces
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #