Locally managed (LMT) vs. Dictionary managed (DMT) tablespace

The LMT is implemented by adding, the extent management local clause to the TABLESPACE definition syntax. Unlike the older dictionary managed tablespaces (DMTs), LMTs automate extent management and keep the Oracle DBA from being Able to specify the next storage parameter to govern extent sizes. The only exception to this rule was when NEXT was used with minextents at table creation time.

In a dictionary managed tablespace (DMT), the data dictionary stores the free space details. While the free blocks list are managed in the segment heard of each table, inside the tablespace), the free space is record Ed in the sys.uet$ table, while used space in the sys.uet$ table.

But with high dml-rate busy tablespaces The data dictionary became a I/O bottleneck and the movement of the space Manageme  NT out of the data dictionary and into the tablespace has both benefits.  First, the tablespace become independent and can be transportable (transportable tablespaces). Second, locally managed tablespaces remove the o/o contention away from the SYS tablespace.

Segment size Management manual vs Segment size management auto.

Here's how to migrate the SYSTEM tablespace from dictionary managed to local managed.

< Code 2.20? Dbms_space_admin_mig_to_local.sql

Conn pkg/pkg#123

--how to migrate SYSTEM tablespace from dictionary managed to locally managed

--check If you have temporary tablespace other than SYSTEM

Col file_name for A40

Select

file_name,

Tablespace_name

From

Dba_temp_files;

Col Tablespace_name for A30

Select

Tablespace_name,

Contents

From

Dba_tablespaces

where

Contents = ' temporary ';

--check if Undo tablespace is online (if you are using automatic undo management)

Select

Tablespace_name,contents

From

Dba_tablespaces

where

Contents = ' undo ';

--put all tablespace in Read Only mode (does not include temporary tablespace or tablespaces this has rollback segments)

Select

Alter Tablespace ' | | tablespace_name| | ' Read only; '

From

Dba_tablespaces

where

Contents <> ' Temporary '

and

Contents <> ' Undo '

and

Tablespace_name not in (' SYSTEM ', ' sysaux ');

' ALTER tablespace ' | | Tablespace_

-----------------------------------------------------------

Alter TABLESPACE users read only;

Alter Tablespace example Read only;

Alter Tablespace Apps_ts_tx_data Read only;

Alter Tablespace Pkg_data Read only;

Alter Tablespace PKG_IDX Read only;

Alter Tablespace pkg_data_32m Read only;

Alter Tablespace pkg_idx_32m Read only;

Alter Tablespace pkg_data_32m_manual Read only;

--put the database in restricted mode

Alter system enable restricted session;

System Altered

Col host_name for A20

Select

instance_name,

HOST_NAME,

Logins

From

V$instance;

instance_name host_name LOGINS

---------------- -------------------- ----------

ora11g dbms.f2c.com.br restricted

--change the SYSTEM tablespace

exec dbms_space_admin.tablespace_migrate_to_local (' SYSTEM ');

--verify The tablespace extent management

Select

Tablespace_name,

Extent_management

From

Dba_tablespaces

where

Tablespace_name = ' SYSTEM ';

Tablespace_name extent_management

------------------------------ -----------------

SYSTEM Local

--disable restricted mode

Alter system disable restricted session;

System Altered

--put tablespaces in Reead write mode

Select

' Alter Tablespace ' | | Tablespace_name | | ' Read write; '
From

Dba_tablespaces
where

Contents <> ' Temporary '
and

Contents <> ' Undo '
and

Tablespace_name not in (' SYSTEM ', ' sysaux ');

' Altertablespace ' | | Tablespace_

------------------------------------------------------------

Alter tablespace users read write;

Alter Tablespace example Read write;

Alter Tablespace Apps_ts_tx_data Read write;

Alter Tablespace Pkg_data Read write;

Alter Tablespace PKG_IDX Read write;

Alter Tablespace pkg_data_32m Read write;

Alter Tablespace pkg_idx_32m Read write;

Alter Tablespace Pkg_data_32m_manual Read write;

Http://www.dba-oracle.com/t_packages_dbms_lmt_vs_dmt.htm

Locally managed (LMT) vs. Dictionary managed (DMT) tablespace