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 LOCAL or 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 UNIFORM and 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 NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for extents that are managed locally.

Creation
Maintenance

Creation

The example below shows the four ways a tablespace can be created, including the default creation which is the same as using the DICTIONARY clause:

-- 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:\Oracle\Oradata\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;

Maintenance

The 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
EXECUTE Dbms_Space_Admin.Tablespace_Migrate_From_Local('TSH_DATA_3');

-- Dictionary to local
EXECUTE Dbms_Space_Admin.Tablespace_Migrate_To_Local('TSH_DATA_3');

Hope this helps. Regards Tim...

Back to the Top.
/