Tablespaces in Oracle

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, 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.

There are three types of tablespaces in Oracle:

• Permanent tablespaces
• Undo tablespaces
• Temporary tablespaces

A tablespace is created with the create tablespace sql command.

Dropping a tablespace

Dropping a tablespace is a structural change to the database that requires a backup.

The extent size of the objects in the tablespace is subject to the tablespace's minimum extent size.

Quotas on tablespaces

Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota...

Tablespace groups

This is a feature that is available with Oracle 10g.

Assigning a tablespace to a tablespace group:

alter tablespace ts_user tablespace group ts_grp_user;

Renaming Tablespaces

This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

alter tablespace ts_user rename to ts_user_01;

The system and sysaux tablespace cannot be renamed, though. But that will not be much of a problem, will it? However, read only tablespaces can be renamed.

After renaming a tablespace, the controlfiles should immediately be backed up.

The system tablespace

The system tablespace is always available when a database is open (it cannot be taken offline).

The system tablespace stores the data dictionary (or their base tables, respectively).

The sysaux tablespace

The sysaux tablespace is new with Oracle 10g. It is used to store database components that were stored in the system tablespace in prior releases of the database. Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g. Additionally, it is the place where automatic workload repository stores its information.

Occupants

An occupant is a set of (application-) tables within the sysaux tablespace. The occupants can be viewed with the v$sysaux_occupants

Bigfile tablespaces

This is a feature of Oracle 10g. A bigfile tablespace contains only one datafile (or tempfile) which can be as big as 2^32 (=4GB) blocks.

create bigfile tablespace beeeg_ts data file '/u01/dat/beeeg.dbf' size 2T

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management (which is the default setting since Oracle 9i). There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.

The system and sysaux tablespace cannot be created as bigfile tablespace.

Bigfile tablespaces should be used with automatic storage management (ASM), or other logical volume managers that support dynamically extensible logical volumes, striping and RAID.

Smallfile tablespaces

A smallfile tablespace is a traditional tablespace that can contain up to 1022 datafiles.

Default tablespaces

An Oracle database can be configured with a default tablespace and a default temporary tablespace. These are used for users who are not configured with default tablespaces or default temporary tablespaces respectively.

Displaying tablespaces

The dba_tablespaces view displays all tablespaces along with the respective relevant information.

Space management

Oracle maintains extents for a tablespace. There are two different methods for Oracle to keep track of free and used (occupied) extents:

• Dictionary managed tablespace
• Locally managed tablespace

It's not possible to alter the space allocation method of a tablespace after it has been created. (Update 10g R2: it seems that is now possible with dbms_space_admin.)

Dictionary managed tablespaces

Extents are allocated according to the following storage parameters

• initial
• next
• pctincrease

The information about used and free extents is stored in the dictionary.

Locally managed tablespaces

A 'bitmap' is stored within the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free or used.

The extent sizes are either uniform or autoallocate. Hence, the following storage parameters don't make sense and are not permitted:

• next
• pctincrease
• minextents
• maxextents
• default storage

Locally managed tablespaces have two advantages: recursive space management is avoided and adjacent free space is automatically coalesced.

Tablespace count limits

As per 10g, a database cannot have more than 65536 tablespaces.

A bigfile tablespace can address 4294967296 (or 232) db blocks. A smallfile tablespace can address 4194304 (or 222) db blocks.

State of tablespaces

A tablespace is either online (ready to be accessed) or offline (not accessible).

A tablespace becomes offline either

• when the DBA explicitly makes the tablespace offline, or
• When an error occurs while Oracle accesses a datafile within the tablespace.

Offline tablespaces cannot be transported to other databases.

Read Only Tablespaces

Read only tablespaces have the benefit that they need be backed up only once.