Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory. These tablespaces also improve the efficiency of space management operations during sorts.

When the SYSTEM tablespace is locally managed, a default temporary tablespace is included in the database by default during database creation. A locally managed SYSTEM tablespace cannot serve as default temporary storage.

Note:

You cannot make a default temporary tablespace permanent.

You can specify a user-named default temporary tablespace when you create a database by using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement. If SYSTEM is dictionary managed, and if a default temporary tablespace is not defined at database creation, then SYSTEM is the default temporary storage. However, the database writes a warning in the alert log saying that a default temporary tablespace is recommended.

Tablespace Modes

The tablespace mode determines the accessibility of the tablespace.

Read/Write and Read-Only Tablespaces

Every tablespace is in a write mode that specifies whether it can be written to. The mutually exclusive modes are as follows:

■Read/write mode

Users can read and write to the tablespace. All tablespaces are initially created as read/write. The SYSTEM and SYSAUX tablespaces and temporary tablespaces are permanently read/write, which means that they cannot be made read-only.

■Read-only mode

Write operations to the data files in the tablespace are prevented. A read-only tablespace can reside on read-only media such as DVDs or WORM drives.

Read-only tablespaces eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces do not change and thus do not require repeated backup. If you recover a database after a media failure, then you do not need to recover read-only tablespaces.

Online and Offline Tablespaces

A tablespace can be online (accessible) or offline (not accessible) whenever the database is open. A tablespace is usually online so that its data is available to users. The SYSTEM tablespace and temporary tablespaces cannot be taken offline.

A tablespace can go offline automatically or manually. For example, you can take a tablespace offline for maintenance or backup and recovery. The database automatically takes a tablespace offline when certain errors are encountered, as when the database writer (DBW) process fails in several attempts to write to a data file. Users trying to access tables in an offline tablespace receive an error.

When a tablespace goes offline, the database does the following:

■The database does not permit subsequent DML statements to reference objects in the offline tablespace. An offline tablespace cannot be read or edited by any utility other than Oracle Database.

■Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level.

■The database saves undo data corresponding to those completed statements in a deferred undo segment in the SYSTEM tablespace. When the tablespace is brought online, the database applies the undo data to the tablespace, if needed.

Tablespace File Size