Examples
The examples below are tried on database version
8.1.7.0.0 with block size of 8K.
(1) To create a new LMT with uniform extents of 32K
click for full example
(2) To create a new LMT that is SYSTEM managed.
click for full example
(3) To find the list of DMTs in the database.
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where extent_management= 'DICTIONARY';
TABLESPACE_NAME STATUS CONTENTS
-------------------- --------- ---------
SYSTEM ONLINE PERMANENT
RBS ONLINE PERMANENT
USERS ONLINE PERMANENT
TEMP ONLINE TEMPORARY
TOOLS ONLINE PERMANENT
INDX ONLINE PERMANENT
DRSYS ONLINE PERMANENT
(4) To find the list of LMTs in the database.
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where extent_management= 'LOCAL';
TABLESPACE_NAME STATUS CONTENTS
-------------------- --------- ---------
OEM_REPOSITORY ONLINE PERMANENT
USERS2 ONLINE PERMANENT
USERS3 ONLINE PERMANENT
(5) Migrating DMT to LMT. Please note the error
given for wrong allocation unit size provided.
SQL> select tablespace_name, status, contents, extent_management, allocation_type
2 from dba_tablespaces
3 where tablespace_name = 'USERS';
TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN ALLOCATIO
------------------------------ --------- --------- ---------- ---------
USERS ONLINE PERMANENT DICTIONARY USER
SQL> select tablespace_name, status, contents, extent_management, allocation_type
2 from dba_tablespaces
3 where tablespace_name = 'ECXX';
TABLESPACE_NAME |STATUS |CONTENTS |EXTENT_MAN|ALLOCATIO
______________________________|_________|_________|__________|_________
ECXX |ONLINE |PERMANENT|DICTIONARY|USER
SQL> exec dbms_space_admin.tablespace_migrate_to_local('ECXX', 512);
BEGIN dbms_space_admin.tablespace_migrate_to_local('ECXX', 512); END;
*
ERROR at line 1:
ORA-03241: Invalid unit size
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1
SQL> exec dbms_space_admin.tablespace_migrate_to_local('ECXX');
PL/SQL procedure successfully completed.
(6) Migrating tablespace from LMT to DMT
To migrate from LMT to DMT. The tablespace should be
online and read write during migration.
SQL> select tablespace_name, status, contents, extent_management, allocation_type
2 from dba_tablespaces
3 where tablespace_name = 'ECXX';
TABLESPACE_NAME |STATUS |CONTENTS |EXTENT_MAN|ALLOCATIO
______________________________|_________|_________|__________|_________
ECXX |ONLINE |PERMANENT|LOCAL |USER
SQL> exec dbms_space_admin.tablespace_migrate_from_local('ECXX');
PL/SQL procedure successfully completed.
SQL> select tablespace_name, status, contents, extent_management, allocation_type
2 from dba_tablespaces
3 where tablespace_name = 'ECXX';
TABLESPACE_NAME |STATUS |CONTENTS |EXTENT_MAN|ALLOCATIO
______________________________|_________|_________|__________|_________
ECXX |ONLINE |PERMANENT|DICTIONARY|USER
(7) Creating LMT with default clause, this would
result in an error.
SQL> create tablespace users3
2 datafile 'D:\oracle\oradata3\users3.dbf' size 5M
3 autoextend off
4 extent management local uniform size 32K
5 default storage (initial 32K next 32k minextents 1 maxextents unlimited pctincrease 10);
create tablespace users3
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy
(8) Converting dictionary managed temporary tablespace
is not supported as of Oracle 8.1.7
SQL> exec dbms_space_admin.tablespace_migrate_to_local('TEMPTM');
BEGIN dbms_space_admin.tablespace_migrate_to_local('TEMPTM'); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1
(9) Storage parameters do not play a role in UNIFORM
and SYSTEM LMTs as extents are handled at tablespace level.
SQL> alter table am1 storage(next 100k);
alter table am1 storage(next 100k)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted
(10) COMPATIBLE parameter should be set to 8.1.6.0.0
or greater when migrating tablespaces.
SQL> select name, value from v$parameter where name = 'compatible';
NAME VALUE
---------------------------------------------------------------- ---------
compatible 8.1.0
SQL> exec dbms_space_admin.tablespace_migrate_to_local('users', 512);
BEGIN dbms_space_admin.tablespace_migrate_to_local('users', 512); END;
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 8.1.6.0.0 or greater
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1
Notes
1. To move an existing DMT to LMT without losing any
of the LMT features, you may consider creating a new LMT and then moving the
objects from the existing DMT to it. This way both uniform extent allocation
and local management of extents features are available.
2. As of Oracle 8.1.7, SYSTEM tablespace cannot be
Dictionary managed. It is supported in higher releases.
3. SMON Process coalesces only DMT tablespaces every
5 minutes, where pctincrease is not set to 0.
4. As of Oracle 8.1.5, it is possible to create LMTs
but not possible to migrate an existing DMT to LMT.
5. As of Oracle 8.1.6, it is possible to create and
migrate to LMT.
6. Tablespaces are by default created as LMTs in
Oracle 9i,
7. SYSTEM tablespace is restricted to LMT.
Creating or migrating the SYSTEM to LMT is a no
return process. Make sure that all the existing DMTs are first converted to LMT
before converting SYSTEM tablespace. If any DMT is present in the database
after conversion of the SYSTEM to LMT, then it will be marked as READ-ONLY and
it cannot be changed to READ-WRITE. The SYSTEM once created or converted to LMT
cannot be converted back to DMT. Once the SYSTEM is LMT no more DMTs can be
created in the database.
8. Once all the tablespaces are converted to LMTs,
the table FET$ would not contain any more records.
Conclusion
LMT is highly beneficial and powerful feature. The
management of object extents will become much easier. With implementation of LMTs,
one should re-evaluate and revise the extent management and object sizing
policies that were followed for DMTs.
Previous
Prev [1] [2] [3] [4]
