Quick Search:
 
 The Oracle TABLESPACE Keyword      [Return To Index] Jump to:  
FULL A - Z Oracle Function Library (formerly named "Morgan's Library")

Term: TABLESPACE

Definition:
A tablespace is a logical storage unit within an Oracle database. It is considered logical because a tablespace is not visible in the file system of the machine on which the database resides. The tablespace itself is made up of one or more datafiles which are stored in the server's file system. A datafile belongs to one and only one tablespace (the datafiles are not shared among tablespaces).

Every table, index, and other object stored in an Oracle database belong to a tablespace. The tablespace acts as a link or conduit between the database and the physical file system where the table's or index' data is stored.

For more information on tablespace options, visit our Oracle Tablespaces page.

Types Of Tablespaces
Oracle uses three types of tablespaces:
  • Permanent tablespaces
  • Undo tablespaces
  • temporary tablespaces

Creating Tablespaces
Tablespaces are created using the CREATE TABLESPACE command as shown below: 

CREATE TABLESPACE <tablespace_name>

The only mandatory parameter in the CREATE TABLESPACE statement is the tablespace name, but there are many options that can be used to fine-tune the tablespace.

This CREATE TABLESPACE statement creates a tablespace 150 megabytes in size named "ts_tablespace", with the tablespace file itself named 'ts_tablespace.dbf', located on the C: drive, under the directory '\data\':

CREATE TABLESPACE ts_tablespace DATAFILE 'c:\data\ts_tablespace.dbf' SIZE 150M


Dropping A Tablespace
Dropping a tablespace is done using the DROP TABLESPACE command as shown below. Note that dropping a tablespace produces a structural change to the database that cannot be reversed without a backup.

DROP TABLESPACE ts_tablespace;


Quotas On Tablespaces
Users can be assigned quotas (limits) on on the space they may use for tablespaces. This quota can be set using the ALTER USER QUOTA command as shown below:

ALTER USER bob QUOTA 200M ON ts_tablespace;


Tablespace Groups
Starting with with Oracle 10g, you may assign a tablespace to a tablespace group:

ALTER TABLESPACE ts_tablespace TABLESPACE GROUP ts_group_alpha;


Renaming Tablespaces
Another feature that became available with Oracle 10g is the ability to rename a tablespace "on-the-fly":

ALTER TABLESPACE ts_tablespace RENAME TO ts_old_tablespace;

Read-only tablespaces can be renamed, but the system and sysaux tablespace cannot be renamed.

The System Tablespace
The system tablespace stores the data dictionary and is available whenever a database is open. Unlike other tablespaces, it cannot be taken offline.

The Sysaux Tablespace
Also new with Oracle 10g is the sysaux tablespace. The sysaux tablespace is used to store database components that were previously stored in the system tablespace in releases prior to version 10g.

Bigfile Tablespaces
Another new feature of Oracle 10g is the bigfile tablespace. A bigfile tablespace contains a single datafile which can address up to 4,294,967,296 db blocks. A bigfile tablespace two terabytes in size is created with the BIGFILE command shown below:

CREATE BIGFILE TABLESPACE bigfile_data DATA FILE '/tspace/data/bigfile_data.dbf' size 2T

With two exceptions, only locally managed tablespaces with automatic segment-space management can be created as bigfile tablespaces. The two exceptions are locally managed undo tablespaces and temporary tablespaces.

In general, bigfile tablespaces should always be used with automatic storage management that support dynamically extensible logical volumes. The use of striping and RAID is also recommended to improve the overall performance of bigfile tablespaces. Note that the system and sysaux tablespace cannot be created as bigfile tablespaces.

Smallfile Tablespaces
A smallfile tablespace is the usual type of tablespace, can contain up to 1,022 datafiles, and can address up to 222 db blocks.

Locally Managed Tablespaces
A 'bitmap' is stored in the tablespace, and each bit in the bitmap determines if a corresponding extent in the tablespace is free or used. Because the extent sizes are of the type uniform or autoallocate, the following storage parameters aren't applicable and therefore are not permitted:
  • next
  • minextents
  • maxextents
  • pctincrease
  • default storage
Locally managed tablespaces have two basic advantages: adjacent free space is automatically coalesced, and recursive space management is avoided.

Related Links:
Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org