Types of Tablespaces

• There are two types of tablespaces in an Oracle database: SYSTEM and Non-System
• The SYSTEM tablespace is created during Database creation
• Its purpose is to hold the data dictionary tables, views, and stored program units.
• It also holds the SYSTEM undo segment.
• It is advisable that User Data should never be stored in the SYSTEM tablespace
• The Non-System tablespaces are created by a DBA
• The Non-System tablespaces include Undo Tablespace, Temporary Tablespaces, Default Temporary Tablespaces and any other DBA-created tablespace to hold User Data

Undo Tablespace

• Undo Tablespace stores only Undo Segments
• Only Oracle has direct control over Undo Tablespace
• Undo Tablespace contains only one Data File

The CREATE TABLESPACE statement for undo tablespaces follows:

CREATE UNDO TABLESPACE mydb1_undo1
DATAFILE ‘/mydatabases/mydb1/mydb1_undo1.dbf’
SIZE 100M;

Temporary Tablespaces

• Oracle uses temporary tablespaces for sort operations
• Temporary tablespaces cannot contain any other type of data
• Temporary tablespaces cannot contain any permanent objects
• The sort segment (Temporary Segment) in the temporary tablespace is created at the time of the first large sort operation in the instance
• This segment (Temporary Segment) expands by allocating extents until the segment is equal to or greater than the total storage demands of all the active sorts running in the given instance
• Temporary tablespaces are always created with NOLOGGING mode enabled
• Temporary tablespaces can never be set to read-only
• Temporary tablespaces’ data files (known as tempfiles) can never be renamed
• To optimize the performance of sorts, the temporary tablespaces’ UNIFORM EXTENT SIZE should be used and should be a multiple of SORT_AREA_SIZE

The following is an example of the CREATE TABLESPACE statement for temporary tablespaces:

CREATE TEMPORARY TABLESPACE mydb1_temp
TEMPFILE ‘/mydatabases/mydb1/mydb1_temp01.dbf’
SIZE 1000M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M;

Default Temporary Tablespaces

• To avoid using SYSTEM tablespace for sorting operations, a Default Temporary Tablespace in the database is needed
• All users created without the TEMPORARY TABLESPACE clause will have the defined Default as their Temporary Tablespace
• Default Temporary Tablespace is always Locally Managed when created during Database creation
• The DEFAULT TEMPORARY TABLESPACE can be reassigned to a database user by use of the ALTER DATABASE command
• After a default temporary tablespace has been defined, it cannot be dropped until a new temporary tablespace is defined
• It cannot be taken offline
• It cannot be made read-only
• It cannot be altered to make it a permanent tablespace

To set a tablespace as the default temporary tablespace after the database has been created, the following command creates a Deafult Temporary Tablespace:

ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE mydb_temp1;

Advertisement