René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Tablespaces [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 filesystem in which the table's or index' data is stored.
There are three types of tablespaces in Oracle:
A tablespace is created with the create tablespace sql command.
See adding tablespaces to primary servers if you want to add a tablespace to a primary server in a standby environment.

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 are 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 immediatly 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

Transportable tablespaces

See transportable tablespaces

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 '/o1/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, 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.

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:
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. See also datafile count limits.
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 explicitely makes the tablespace offline, or
  • when an error occures 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.

Thanks

Thanks also to Dave Wu who found a broken link on this page.
Thanks also to Sridhar Subramanian, Virk Istifad and Muhammed Imran who each spotted a typo on this page.
Thanks also to Murali Bobba who notified me of an error on this page.