8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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;
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...