Understanding Oracle's Locally Managed Tablespaces - Page 4

June 23, 2003

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

(2) To create a new LMT that is SYSTEM managed.

(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 restrictions as 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.