SQL Statements: CREATE SYNONYM to CREATE TRIGGER, 5 of 6

CREATE TEMPORARY TABLESPACE

Purpose

Use the CREATE TEMPORARY TABLESPACE statement to create a locally managed 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.

To create a temporary tablespace that is dictionary managed, use the CREATE TABLESPACE statement with the TEMPORARY clause.

Note:

Media recovery does not recognize tempfiles.

See Also:

CREATE TABLESPACE for information on creating tablespaces to store persistent schema objects and dictionary-managed temporary tablespaces
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 statements_762.gif follows

Text description of create_temporary_tablespace

(datafile_tempfile_spec::=, temp_tablespace_extent::=)

temp_tablespace_extent::=

Text description of statements_763.gif follows

Text description of temp_tablespace_extent

Semantics

tablespace

Specify the name of the temporary tablespace.

TEMPFILE datafile_tempfile_spec

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. The file has AUTOEXTEND enabled and an unlimited maximum size. If the DB_CREATE_FILE_DEST parameter is not set, then 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. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation.

See Also:

file_specification for a full description, including the AUTOEXTEND parameter

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 created with the CREATE TEMPORARY TABLESPACE statement have locally managed extents, so this clause is optional. To create a dictionary-managed temporary tablespace, use the CREATE TABLESPACE statement with the TEMPORARY clause.

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, then Oracle uses the default extent size of 1M.

See Also:

Oracle9i Database Concepts for a discussion of locally managed tablespaces

Example

Creating a Temporary Tablespace: Example

This statement shows how the temporary tablespace that serves as the default temporary tablespace for database users in the sample database was created:

CREATE TEMPORARY TABLESPACE temp_demo
   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 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 = '$ORACLE_HOME/rdbms/log';

CREATE TEMPORARY TABLESPACE tbs_05;