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.

Contents

[edit] History

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

[edit] Check if a tablepsace is dictionary managed

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.

[edit] Coalescing of free space

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.

[edit] Converting between DMT and LMT

From DMT to LMT:

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

From LMT to DMT:

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

[edit] Also see

  • 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 #