Managing Tablespaces : Temporary Tablespaces

A temporary tablespace contains transient data that persists only for the duration of
the session. Temporary tablespaces can improve the concurrency of multiple sort
operations, reduce their overhead, and avoid Oracle Database space management operations. A temporary tablespace can be assigned to users with the CREATE USER or ALTER USER statement and can be shared by multiple users.

Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that
performs sort operations within a given tablespace. The sort segment is created by the
first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.

You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies the current sort users in those segments.

You cannot explicitly create objects in a temporary tablespace.

Note:

The exception to the preceding statement is a temporary table. When you create a temporary table, its rows are stored in your default temporary tablespace.

Temporary Tablespaces

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. A temporary tablespace can be assigned to users with the CREATE USER or ALTER USER statement and can be shared by multiple users.

Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.

You can view the allocation nd deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies the current sort users in those segments.
You cannot explicitly create objects in a temporary tablespace.

Note:
The exception to the preceding statement is a temporary table. When you create a temporary table, its rows are stored in your default temporary tablespace. 

Creating a Locally Managed Temporary Tablespace
Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data.
Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database. You also use different views for viewing information about tempfiles than you would for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.
To create a locally managed temporary tablespace, you use the CREATE TEMPORARY TABLESPACE statement, which requires that you have the CREATE TABLESPACE
system privilege.

The following statement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the standard block size is 2K) is represented by a bit in the bitmap for the file.

CREATE TEMPORARY TABLESPACE lmtemp

TEMPFILE '/u02/oracle/data/lmtemp01.dbf' SIZE 20M REUSE

EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 16M;

The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The
Oracle Database default for SIZE is 1M. But if you want to specify another value for SIZE, you can do so as shown in the preceding statement.  

The AUTOALLOCATE clause is not allowed for temporary tablespaces.

Note:
On some operating systems, the database 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 the database allocates tempfile space in this way on your system.

Creating a Bigfile Temporary Tablespace
Just as for regular tablespaces, you can create single-file (bigfile) temporary
tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single-tempfile tablespace. 

Altering a Locally Managed Temporary Tablespace
Note:
You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary
tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a
locally managed temporary tablespace.

Except for adding a tempfile, taking a tempfile offline, or bringing a tempfile online, as
illustrated in the following examples, you cannot use the ALTER TABLESPACE
statement for a locally managed temporary tablespace.

ALTER TABLESPACE lmtemp

ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;

ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;

ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

Note:
You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. The  view V$TEMPFILE displays online status for a tempfile.
However, the ALTER DATABASE statement can be used to alter tempfiles.
The following statements take offline and bring online tempfiles. They behave
identically to the last two ALTER TABLESPACE statements in the previous example.

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

The following statement resizes a temporary file:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a temporary file and deletes the operating system file:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP

INCLUDING DATAFILES;

The tablespace to which this tempfile belonged remains. A message is written to the alert log for the datafile that was deleted. If an operating system error prevents the deletion of the file, the statement still succeeds, but a message describing the error is written to the alert log.

It is also possible to use the ALTER DATABASE statement to enable or disable the automatic extension of an existing tempfile, and to rename (RENAME FILE) a tempfile.

Note:

To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the ALTER DATABASE RENAME FILE command to update the database controlfiles.

Multiple Temporary Tablespaces: Using Tablespace Groups

A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics: 

• It contains at least one tablespace.
       There is no explicit limit on the maximum number of tablespaces that are
       contained in a group.
• It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
• You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.

You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel
execution servers in a single parallel operation to use multiple temporary tablespaces.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.

Creating a Tablespace Group

You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.

For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

CREATE TEMPORARY TABLESPACE lmtemp2

TEMPFILE '/u02/oracle/data/lmtemp201.dbf' SIZE 50M
TABLESPACE GROUP group1;

ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

Changing Members of a Tablespace Group

You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement.

The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.

CREATE TEMPORARY TABLESPACE lmtemp3

TEMPFILE '/u02/oracle/data/lmtemp301.dbf' SIZE 25M
TABLESPACE GROUP group1;

The following statement also adds a tablespace to an existing group, but in this
case because tablespace lmtemp2 already belongs to group1, it is in effect moved
from group1 to group2:

ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3. 

You can remove a tablespace from a group as shown in the following statement:

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.

Assigning a Tablespace Group as the Default Temporary Tablespace

Use the ALTER DATABASE...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:

ALTER DATABASE sample DEFAULT  TEMPORARY TABLESPACE group2;

Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2.

If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first remove the tablespace from the
tablespace group. Likewise, you cannot drop a single temporary tablespace as
long as it is the default temporary tablespace.