CREATE TEMPORARY TABLESPACE

Purpose

Use the CREATE TEMPORARY TABLESPACE statement to create a temporary tablespace, which is an allocation of space in the database that can contain schema objects for the duration of a session.

To create a tablespace to contain persistent schema objects, use the CREATE TABLESPACE statement.

See Also: CREATE TABLESPACE

Prerequisites

You must have the CREATE TABLESPACE system privilege.

Syntax

filespec: See filespec.

autoextend_clause::=

maxsize_clause::=

Keywords and Parameters

tablespace

Specify the name of the temporary tablespace.

TEMPFILE filespec

Specify the tempfiles that make up the tablespace.

Note: Media recovery does not recognize tempfiles.

See Also: filespec

autoextend_clause

The autoextend_clause lets you enable or disable the automatic extension of the tempfile.

OFF	Specify OFF to disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND statements.
ON	Specify ON to enable autoextend.
NEXT integer	Specify the disk space to allocate to the tempfile when more extents are required.
maxsize_clause	The maxsize_clause lets you specify the maximum disk space allowed for allocation to the tempfile.
	integer: Specify in bytes the maximum disk space allowed for allocation to the tempfile. Use K or M to specify this space in kilobytes or megabytes.
	UNLIMITED: Specify UNLIMITED to set no limit on allocating disk space to the tempfile.

EXTENT MANAGEMENT LOCAL

The EXTENT MANAGEMENT clause lets you specify that the tablespace is locally managed, meaning that some part of the tablespace is set aside for a bitmap.

UNIFORM integer	Specify the size of the extents of the temporary tablespace in bytes. All extents of temporary tablespaces are the same size (uniform). If you do not specify this clause, Oracle uses uniform extents of 1M.
SIZE integer	Specify in bytes the size of the tablespace extents. Use K or M to specify the size in kilobytes or megabytes. If you do not specify SIZE, Oracle uses the default extent size of 1M.

Example

Temporary Tablespace Example

This statement creates a temporary tablespace in which each extent is 16M.

CREATE TEMPORARY TABLESPACE tbs_1 TEMPFILE 'file_1.f'
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

If we assume the default database block size of 2K, and that each bit in the map represents one extent, then each bit maps 8,000 blocks.