Oracle temporary tablespace groups

Oracle Database Tips by Donald Burleson

By Mike Ault

Oracle 10g introduces a new term called a temporary tablespace group.

Temporary Tablespace Group Overview

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.

Temporary Tablespace Group Benefits

Temporary tablespace group has the following benefits:

• 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.

New Data Dictionary View

Oracle 10g introduces a new data dictionary view, dba_tablespace_groups, for the temporary tablespace group. 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

Examples

Example 1: 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;

Example 2: 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;

Example 3: 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 ??;

Example 4: Add a temporary tablespace to a temporary tablespace group.

     ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Example 5: Assign a user to a default temporary tablespace group.

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

     ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;