|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02
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
To create a temporary tablespace that is dictionary managed, use the
TABLESPACE statement with the
You must have the
TABLESPACE system privilege.
Specify the name of the temporary tablespace.
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
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.
temp_tablespace_extent clause lets you specify how the tablespace is managed.
This clause indicates that some part of the tablespace is set aside for a bitmap. All temporary tablespaces created with the
TABLESPACE statement have locally managed extents, so this clause is optional. To create a dictionary-managed temporary tablespace, use the
TABLESPACE statement with the
All extents of temporary tablespaces are the same size (uniform), so this keyword is optional. However, you must specify
UNIFORM in order to specify
Specify in bytes the size of the tablespace extents. Use
M to specify the size in kilobytes or megabytes.
If you do not specify
SIZE, then Oracle uses the default extent size of 1M.
Oracle9i Database Concepts for a discussion of locally managed tablespaces
This statement shows how the temporary tablespace that serves as the default temporary tablespace for database users in the sample database was created:
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):