Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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.
See Also:
|
You must have the CREATE
TABLESPACE
system privilege.
create_temporary_tablespace::=
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 TEMPFILE
clause.
See Also:
|
The 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 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.
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
.
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 |
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;