For more information on tablespace options, visit our Oracle Tablespaces page.
Types Of Tablespaces
Oracle uses three types of 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;
Starting with with Oracle 10g, you may assign a tablespace to a tablespace group:
ALTER TABLESPACE ts_tablespace TABLESPACE GROUP ts_group_alpha;
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.
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.
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:
Related Code Snippets: