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.
Hope this helps! Rupam
