Oracle temporary tablespace groups (10g)

A temporary tablespace group consists of only temporary tablespace, and has the following properties:

1. It contains one or more temporary tablespaces.
2. It contains only temporary tablespace.
3. It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group.

Temporary tablespace group has the following benefits:

1. It allows multiple default temporary tablespaces to be specified at the database level.
2. It allows the user to use multiple temporary tablespaces in different sessions at the same time.
3. It allows a single SQL operation to use multiple temporary tablespaces for sorting.

Using a tablespace with a temporary tablespace group will result in the following select statement. However, using a tablespace without a temporary tablespace group will not return the select statement below.
select
tablespace_name, group_name
from
DBA_TABLESPACE_GROUPS;
TABLESPACE_NAME GROUP_NAME
--------------- -----------
TEMP01 TEMPGROUP_A
TEMP02 TEMPGROUP_A
TEMP03 TEMPGROUP_B

Example:
- Create a temporary tablespace and implicitly add it to a temporary tablespace group.

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u02/oradata/temp01.dbs’ SIZE 500M
TABLESPACE GROUP tempgroup_a;

- Create a temporary tablespace without assigning it to a temporary tablespace group.

The following two statements are exactly equivalent:

CREATE TEMPORARY TABLESPACE temp04
TEMPFILE ‘/u02/oradata/temp04.dbs’ SIZE 200M
TABLESPACE GROUP ‘’;

CREATE TEMPORARY TABLESPACE temp04
TEMPFILE ‘/u02/oradata/temp04.dbs’ SIZE 200M;

- Remove a temporary tablespace from a temporary tablespace group.

This statement will remove temporary tablespace temp04 from its original temporary tablespace group:

ALTER TABLESPACE temp04 TABLESPACE GROUP ‘‘;
- Add a temporary tablespace to a temporary tablespace group.

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

- Assign a user to a default temporary tablespace group.

ALTER USER USER TEMPORARY TABLESPACE tempgroup_A;

In this case, user 'USER' will have multiple default temporary tablespaces (see Figure). A single SQL operation by 'USER' can use more than one temporary tablespace for sorting.