Introduction

Traditionally, up to Oracle8 Release 8.0.5 the management of free and used extents of a tablespace was done in the data dictionary. These “traditional” tablespaces are also called “Dictionary Managed Tablespaces”, in order to differentiate them from a new type of table space called Locally Managed Tablespaces introduced in Oracle8i Release 8.1.5. All extent information in Locally Managed Tablespaces is tracked by bitmaps in the datafiles of a tablespace.

Dictionary Managed Tablespaces

In the traditional or Dictionary Managed Tablespaces, the data dictionary does extent management. When extents are allocated or freed, data dictionary tables are updated and rollback information about each dictionary table update is maintained. All data dictionary operations are subject to the same space management considerations as any other “user” space maintenance – for example, rollback activity due to updates of these dictionary tables could cause the rollback segment to extend causing more space management operations. This was the only option available before release 8.1.5.

Locally Managed Tablespaces

Bitmaps manage space allocation very efficiently, and require no dictionary access to allocate an extent to an object. In Locally Managed Tablespaces, bitmaps provide extent management. When extents are allocated or freed, a bitmap is updated to indicate the freed or used status of blocks in a datafile. Each datafile in a locally managed tablespace has its own bitmap. Each bit in the bitmap tracks a block or a group of blocks in an extent. When an extent is allocated or freed, the bitmap is changed to reflect the new status of these blocks in that extent. These bitmap changes do not generate rollback information for these “space management” operations. This is a new option available from Oracle8i Release 8.1.5.

Extent Allocation in Locally Managed Tablespaces

The LOCAL clause of the EXTENT MANAGEMENT clause specifies this method of space management in the tablespace CREATE statement.

Extents in Locally Managed Tablespaces can be created specifying either

UNIFORM SIZE - where all extents are the same size, or, AUTOALLOCATE - where extents are automatically sized by the system at tablespace creation time.

If AUTOALLOCATE or UNIFORM is not specified, then AUTOALLOCATE is the default.

Create a Dictionary Managed Tablespace

CREATE TABLESPACE dmt
DATAFILE 'dmt01.dbf' SIZE 100M REUSE
EXTENT MANAGEMENT DICTIONARY

DEFAULT ;
If “EXTENT MANAGEMENT LOCAL” is not specified then the tablespace is managed using dictionary tables. DICTIONARY is the default.

Create an AUTOALLOCATED Extent Locally Managed Tablespace

Oracle determines the optimal size of the additional extents when the keyword AUTOALLOCATE is specified. The minimum extent size is 64KB.

CREATE TABLESPACE lmt
DATAFILE 'lmt01.dbf' SIZE 100M REUSE
EXTENT MANAGEMENT LOCAL
AUTOALLOCATE;

Create a UNIFORM SIZE Extent Locally Managed Tablespace

For UNIFORM SIZE extents an extent size can be specified. The default size is 1MB.

CREATE TABLESPACE lmt
DATAFILE 'lmt01.dbf' SIZE 100M REUSE
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 256K;

Full article...