Tablespace Management in Oracle.

Share via:

Tablespace Management in Oracle. 

A Tablespace is a logical storage unit within an Oracle database. It is logical because a table space is not visible in the file system of the machine on which the database resides.

A table space, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server.

A datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace.

The tablespace builds the bridge between the Oracle database and the file system in which the table’s or index’ data is stored.

Comparison of TABLESPACE  with FILES(o/s):

Tablespace options in a simple diagram :

Based on Extent management there is two types of tablespaces . They are :

1.Dictionary Managed Tablespace

2.Locally Managed Tablespace

Dictionary Managed Tablespace

Tablespaces that record extent allocation in the dictionary are called Dictionary Managed Tablespaces (DMT).

With this approach the data dictionary contains tables that store information that is used to manage extent allocation and deallocation manually.

Extents are managed in the data dictionary Each segment stored in the tablespace can have different storage clause Coalescing is required.


The DEFAULT STORAGE clause enables you to customize the allocation of extents. This provides increased flexibility, but less efficiency than locally managed tablespaces

The tablespace will be in a single, 50M datafile. The EXTENT MANAGEMENT DICTIONARY clause specifies the management.

All segments created in the tablespace will inherit the default storage parameters unless their storage parameters are specified explicitly to override the default.

The storage parameters specify the following:

INITIAL – size in bytes of the first extent in a segment.

NEXT – size in bytes of second and subsequent segment extents.

PCTINCREASE – percent by which each extent after the second extent grows.

SMON periodically coalesces free space in a dictionary-managed tablespace, but only if the PCTINCREASE setting is NOT zero. Use ALTER TABLESPACE <tablespacename> COALESCE to manually coalesce adjacent free extents.

MINEXTENTS – number of extents allocated at a minimum to each segment upon creation of a segment.

MAXEXTENTS – number of extents allocated at a maximum to a segment – you can specify UNLIMITED.


Locally Managed Tablespace

Tablespaces that record extent allocation in the tablespace header are called Locally Managed Tablespaces (LMT). The extents allocated to a locally managed tablespace are managed through the use of bitmaps.

Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available since Oracle 8i.

It is worth using LMTS considering the benefits in doing so. Each bit corresponds to a block or group of blocks (an extent). The bitmap value (on or off) corresponds to whether or not an extent is allocated or free for reuse.

Reduced contention on data dictionary tables No undo generated when space allocation or deallocation occurs

Benefits of using LMTs include:

  • Locally managed tablespaces do not record free space in the data dictionary, it reduces contention on these tables.
  • Local management of extents automatically tracksadjacent free space, eliminating the need to coalesce free 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.
  • Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
  • Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

Reduced fragmentation No coalescing required.

Based on Contents there are three types of tablespaces. They are :




Permanent tablespaces will contains actual data  or metadata .

Ex: System,Sysaux, Userdata etc…,

A TEMPORARY tablespace is used to manage space for sort operations.

Ex : Temp tablespace

Undo tablespace is used to store before image of DML tx’s.



Based on Sizes there are two  types of tablespaces. They are :

1.Small file

2.Big file


It indicates whether the tablespace is plugged in (YES) or not (NO)

If we created tablespace the default Plugged_in value is NO.

If we perform copied tablespace from one database to another database using Transportable tablespace(T-Ts) then the plugged_in value will be YES.



We can keep tablespace either Offline or Online.


If we keep tablespace Offline then any user cannot perform select ,insert,update and delete operations.

To keep in offline we need follow below syntax


If we keep tablespace Online  then only users can  perform select ,insert,update and delete operations.

To keep in Online we need follow below syntax


There are two modes of tablespace .

1.Read only.

2.Read write


If we keep tablespace Read Only  then  users can  perform select operations but not insert,update and delete operations.

To keep in Read Only we need follow below syntax


If we keep tablespace Read WRITE  then  users can  perform select ,insert,update and delete operations.

To keep in Read Write we need follow below syntax

Data Dictionary Views for Tablespaces.
















Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Add Comment