Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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

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

Note. Later releases changed the default to be MANAGEMENT LOCAL.

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
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...

Back to the Top.