Locally Managed Tablespaces
Extent management has traditionally been controlled by system tables, causing contention in busy systems with lots of inserts and deletes. In Oracle 8i the
EXTENT MANAGEMENT clause was introduced into the
CREATE TABLESPACE statement allowing extent management to be
DICTIONARY. Locally Manages Tablespaces (LMT) have a bitmap of the blocks, or groups of blocks, they contain allowing them to track extent allocation without reference to the data dictionary.
Extent size within LMTs is determined by the
AUTOALLOCATE clauses. If UNIFORM is specified,
all extents within the tablespace will be the same size, with 1M being the default extent size. The
AUTOALLOCATE clause allows you to size the initial extent leaving Oracle to determine the optimum size for subsequent extents, with 64K being the minimum.
The storage parameters
DEFAULT STORAGE are not valid for extents that are managed locally.
The example below shows the four ways a tablespace can be created, including the default creation which is the same as using the
-- Default extent management (DICTIONARY) CREATE TABLESPACE tsh_data_1 DATAFILE 'c:\Oracle\Oradata\TSH1\tsh101.dbf' SIZE 50M; -- Explicit dictionary extent management CREATE TABLESPACE tsh_data_2 DATAFILE 'c:\Oracl\eOradata\TSH1\tsh201.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY; -- Local extent management using autoallocate CREATE TABLESPACE tsh_data_3 DATAFILE 'c:\Oracle\Oradata\TSH1\tsh301.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; -- Local extent management using uniform extents CREATE TABLESPACE tsh_data_4 DATAFILE 'c:\Oracle\Oradata\TSH1\tsh401.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Later releases changed the default to be
DBMS_SPACE_ADMIN package contains a number of maintenance routines for locally managed tablespaces, including routines to convert from local to dictionary management. From Oracle 8.1.6 onwards the conversion from dictionary to local is also possible.
-- Local to dictionary EXEC DBMS_SPACE_ADMIN.tablespace_migrate_from_local('TSH_DATA_3'); -- Dictionary to local EXEC DBMS_SPACE_ADMIN.tablespace_migrate_to_local('TSH_DATA_3');
For more information see:
Hope this helps. Regards Tim...