Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE SYNONYM to CREATE TRIGGER, 5 of 6
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.
You must have the CREATE
TABLESPACE
system privilege.
create_temporary_tablespace::=
create_temporary_tablespace
autoextend_clause
maxsize_clause::=
maxsize_clause
temp_tablespace_extent
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. If this parameter is not set, you must specify the TEMPFILE
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.
Specify ON
to enable autoextend.
Specify OFF
to turn off autoextend if is turned on.
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.
Use the MAXSIZE
clause to specify the maximum disk space allowed for automatic extension of the datafile.
Use the UNLIMITED
clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.
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 have locally managed extents, so this clause is optional.
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
, Oracle uses the default extent size of 1M.
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;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|