A temporary tablespace group consists of only temporary tablespace, and has the following properties:
- It contains one or more temporary tablespaces.
- It contains only temporary tablespace.
- 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.
- It allows multiple default temporary tablespaces to be specified at the database level.
- It allows the user to use multiple temporary tablespaces in different sessions at the same time.
- It allows a single SQL operation to use multiple temporary tablespaces for sorting.
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.