|
LOCALLY MANAGED TABLESPACES IN ORACLE
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 <storage clause>;
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;
How Extents are Allocated
Oracle looks for free space to allocate to a new extent by first determining a
candidate datafile in the tablespace and then searching the datafile's bitmap
for the required number of adjacent free blocks. If that datafile does not have
enough adjacent free space, Oracle looks in another datafile. When extents are
de-allocated, Oracle modifies the bitmap in the datafile.
Advantages
Locally managed tablespaces have the following advantages over
dictionary-managed tablespaces:
-
Local management of extents avoids recursive space
management operations, which can occur in dictionary-managed tablespaces if
consuming or releasing space in an extent results in another operation that
consumes or releases space in a rollback segment or data dictionary table.
-
Local management of extents tracks adjacent free space,
eliminating coalescing free extents.
-
Reliance on data dictionary is reduced. This minimizes
access to the data dictionary, potentially, improving performance and
availability.
Due to these improvements, Oracle recommends using Locally
Managed Tablespaces for all new tablespaces if fragmentation is expected to be
an issue.
Notes:
1. Both dictionary managed and locally managed tablespaces can coexist in the
same database.
2. If EXTENT MANAGEMENT LOCAL is not specified, then the data dictionary manages
the extents of a tablespace. EXTENT MANAGEMENT DICTIONARY is default.
3. Currently, a locally managed SYSTEM tablespace cannot be created (True in
8.1.7) (?? For ROLLBACK SEGMENT usage)
4. EXTENT MANAGEMENT LOCAL
CREATE TABLESPACE statement - for permanent tablespaces
CREATE TEMPORARY TABLESPACE statement - for temporary tablespaces
For example, Locally Managed Temporary tablespaces are created by the
following:
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE 'temp.dbf' SIZE 256M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 256K;
Locally Managed Temporary tablespaces cannot be created by the following
specification:
CREATE TABLESPACE TEMP
DATAFILE 'temp.dbf' SIZE 256M
TEMPORARY
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 256K;
5. When creating an LMT, AUTOALLOCATE is default.
6. Temporary LMT’s can only use UNIFORM SIZE, and not AUTOALLOCATE.
7. The parameters
TEMPORARY,
NEXT,
PCTINCREASE,
MINEXTENTS,
MAXEXTENTS,
MINIMUM EXTENT, or
DEFAULT storage_clause
are not valid for locally managed tablespaces.
Could get an error or be silently ignored!!
8. DBMS_SPACE_ADMIN package provides
Defect Diagnosis and Repair Functionality for LMT
Migration of DMT to LMT or vice-versa
9. LMTs can be altered for many of the same reasons as a dictionary-managed
tablespace. However,
altering storage parameters is not an option and
coalescing free extents is unnecessary
an LMT cannot be altered to a locally managed temporary tablespace.
10. To find out which tablespaces are LMTs
SELECT tablespace_name,
extent_management
FROM dba_tablespaces
WHERE extent_management = 'LOCAL';
|
