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. If you subsequently assign this temporary tablespace to a particular user, then Oracle will also use this tablespace for sorting operations in transactions initiated by that user.

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

Note: Media recovery does not recognize tempfiles.

See Also: CREATE TABLESPACE for information on creating tablespaces to store persistent schema objects CREATE USER for information on assigning a temporary tablespace to a user

Prerequisites

You must have the CREATE TABLESPACE system privilege.

Syntax

create_temporary_tablespace::=

Text description of create_temporary_tablespace

filespec: See filespec.

autoextend_clause::=

Text description of autoextend_clause

maxsize_clause::=

Text description of maxsize_clause

temp_tablespace_extent::=

Text description of temp_tablespace_extent

Keywords and Parameters

tablespace

Specify the name of the temporary tablespace.

TEMPFILE filespec

Specify the tempfiles that make up the tablespace.

You can omit the TEMPFILE clause only if the DB_CREATE_FILE_DEST initialization parameter has been set. In this case, Oracle creates a 100 MB Oracle-managed tempfile in the default file destination specified in the parameter. If this parameter is not set, you must specify the TEMPFILE clause.

Note: On some operating systems, Oracle does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. Please refer to your operating system documentation to determine whether Oracle allocates tempfile space in this way on your system.

See Also: filespec

autoextend_clause

Use the autoextend_clause to enable or disable the automatic extension of a new datafile or tempfile. If you do not specify this clause, these files are not automatically extended.

ON

Specify ON to enable autoextend.

OFF

Specify OFF to turn off autoextend if is turned on.

Note: When you turn off autoextend, the values of NEXT and MAXSIZE are set to zero. If you turn autoextend back on in a subsequent statement, you must reset these values.

NEXT

Use the NEXT clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is the size of one data block.

MAXSIZE

Use the MAXSIZE clause to specify the maximum disk space allowed for automatic extension of the datafile.

UNLIMITED

Use the UNLIMITED clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.

temp_tablespace_extent

The temp_tablespace_extent clause lets you specify how the tablespace is managed.

EXTENT MANAGEMENT LOCAL

This clause indicates that some part of the tablespace is set aside for a bitmap. All temporary tablespaces have locally managed extents, so this clause is optional.

UNIFORM

All extents of temporary tablespaces are the same size (uniform), so this keyword is optional. However, you must specify UNIFORM in order to specify SIZE.

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.

See Also: Oracle9i Database Concepts for a discussion of locally managed tablespaces

Example

Temporary Tablespace Example

This statement creates the temporary tablespace that serves as the default temporary tablespace for database users in the sample database:

CREATE TEMPORARY TABLESPACE temp
   TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

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

The following example sets the default location for datafile creation for datafile creation and then creates a tablespace with an Oracle-managed tempfile in the default location. The tempfile is 100 M and is autoextensible with unlimited maximum size (the default values for Oracle-managed files):

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata/sample';

CREATE TEMPORARY TABLESPACE omf_tempts1;