Oracle Database Administration: The Essential Refe

Temporary tablespaces

Oracle7 introduced the concept of temporary tablespaces, which are used exclusively for Oracle’s sort segments. By eliminating serialization of space management operations involved in the allocation and deallocation of sort space, all operations that use sorts can benefit from improved performance when sorts are too large to fit in memory. These performance gains are particularly significant when running Oracle Parallel Server.

Tip

A temporary tablespace can be used only for sort segments; no permanent objects may be created in a temporary tablespace.

To create a temporary tablespace, use the keyword TEMPORARY in the CREATE TABLESPACE statement. For example, the following statement will create a temporary tablespace called TEMP:

CREATE TABLESPACE TEMP
DATAFILE '/disk99/oracle/oradata/TEST/temp01.dbf' SIZE 50M
DEFAULT STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED)
TEMPORARY;

An existing non-temporary tablespace may be converted to a temporary tablespace by using the SQL statement if it contains no permanent objects:

ALTER TABLESPACE tablespace TEMPORARY;