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 is when NEXT is 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 is managed in the segment heard of each table, inside the tablespace), the Free space is recorded 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 management out of the data dictionary and into the tablespace have two 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 is 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 (do not include temporary tablespace or tablespaces that 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;
