The extent_management_clause
lets you specify how the extents of the tablespace will be managed.
Note:
After you have specified extent management with this clause, you can change extent management only by migrating the tablespace.-
Specify
LOCAL
if you want the tablespace to be locally managed. Locally managed tablespaces have some part of the tablespace set aside for a bitmap. This is the default for permanent tablespaces. Temporary tablespaces are always automatically created with locally managed extents.-
AUTOALLOCATE
specifies that the tablespace is system managed. Users cannot specify an extent size. You cannot specifyAUTOALLOCATE
for a temporary tablespace. -
UNIFORM
specifies that the tablespace is managed with uniform extents ofSIZE
bytes.The defaultSIZE
is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specifyUNIFORM
in order to specifySIZE
. You cannot specifyUNIFORM
for an undo tablespace.
-
-
Specify
DICTIONARY
if you want the tablespace to be managed using dictionary tables.
Restriction on Dictionary-managed Tablespaces You cannot specify DICTIONARY
if the SYSTEM
tablespace of the database is locally managed or if you have specified the temporary_tablespace_clause
.
Note:
Oracle strongly recommends that you create only locally managed tablespaces. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces. The creation of new dictionary-managed tablespaces is scheduled for desupport.If you do not specify the extent_management_clause
, then Oracle Database interprets the MINIMUM
EXTENT
clause and the DEFAULT
storage_clause
to determine extent management.
-
If you do not specify the
DEFAULT
storage_clause
, then the database creates a locally managed autoallocated tablespace. -
If you did specify the
DEFAULT
storage_clause
, then:-
If you specified the
MINIMUM
EXTENT
clause, then the database evaluates whether the values ofMINIMUM
EXTENT
,INITIAL
, andNEXT
are equal and the value ofPCTINCREASE
is 0. If they are equal, then the database creates a locally managed uniform tablespace with extent size =INITIAL
. If theMINIMUM
EXTENT
,INITIAL
, andNEXT
parameters are not equal, or ifPCTINCREASE
is not 0, then the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace. -
If you did not specify
MINIMUM
EXTENT
clause, then the database evaluates only whether the storage values ofINITIAL
andNEXT
are equal andPCTINCREASE
is 0. If they are equal, then the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.
-
Restrictions on Extent Management Extent management is subject to the following restrictions:
-
A permanent locally managed tablespace can contain only permanent objects. If you need a locally managed tablespace to store temporary objects, for example, if you will assign it as a user's temporary tablespace, then use the
temporary_tablespace_clause
. -
If you specify
LOCAL
, then you cannot specifyDEFAULT
storage_clause,
MINIMUM
EXTENT
, or thetemporary_tablespace_clause
.
===================================================================================
The segment_management_clause
is relevant only for permanent, locally managed tablespaces. It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace.
AUTO Specify AUTO
if you want the database to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO
, then the database ignores any specification for PCTUSED
, FREELIST
, and FREELIST
GROUPS
in subsequent storage specifications for objects in this tablespace. This setting is called automatic segment-space management and is the default.
MANUAL Specify MANUAL
if you want the database to manage the free space of segments in the tablespace using free lists. Oracle strongly recommends that you do not use this setting and that you create tablespaces with automatic segment-space management.
To determine the segment management of an existing tablespace, query the SEGMENT_SPACE_MANAGEMENT
column of the DBA_TABLESPACES
or USER_TABLESPACES
data dictionary view.
Notes:
If you specifyAUTO
segment management, then:
-
If you set extent management to
LOCAL
UNIFORM
, then you must ensure that each extent contains at least 5 database blocks. -
If you set extent management to
LOCAL
AUTOALLOCATE
, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.
Restrictions on Automatic Segment-space Management This clause is subject to the following restrictions:
-
You can specify this clause only for a permanent, locally managed tablespace.
-
You cannot specify this clause for the
SYSTEM
tablespace.