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;
|
|
|
Inside the DBMS Packages
The DBMS packages form the foundation of
Oracle DBA functionality. Now, Paulo Portugal writes a landmark book
Advanced Oracle DBMS Packages: The Definitive Reference.
This is a must-have book complete with a code
depot of working examples for all of the major DBMS packages.
Order directly from Rampant and save 30%.
|
|
|
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|