Types of Tablespaces

Tablespaces are of 2 types:

                1.Locally managed tablespace

                2.Dictionary managed tablespace

From 9i, once we create a tablespace by default it is locally managed.

In case of dictionary managed tablespace, information about free extents and free blocks will be stored in the data dictionary.

In case of locally managed tablespace, information about free extents and free blocks will be stored within the tablespace.

Once we create a tablespace, by default it will be created in the read/write mode and by default it is online.

By default, logging is enabled for every tablespace that we create. Generally, no logging is enabled during bulk load operations and index creations.

Note : once the bulk load operations are completed, enable logging for the tablespaces and immediately take the backup of the database.

By default, segment space management is auto. In the case of auto, oracle considers some of the blocks as BMB’s(Bit Map Blocks). These BMB’s indicates the status of the blocks within the extent.

In case of manual(older versions of oracle), oracle uses free lists and free list groups to manage the space.

Every user within the database has to be assigned to a specific tablespace. Prior to 10g, if we create a user without assigning a tablespace, by default, it will be assigned to the system.dbf(not recommended).

In version 10 g, oracle has introduced a default permanent tablespace for the database. If a user is created without explicitly assigning a tablespace, user will be assigned to the default permanent tablespace.

A database contains one default permanent tablespace, one default undo tablespace and default temporary tablespace.