Temporary Tablespace Group

Introduced in Oracle 10g “Temporary Tablespace Group”

Temporary Tablespace Group

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.

Data Dictionary View:  ts$ , dba_tablespace_groups

 Using a tablespace with a temporary tablespace group will result in the following select statement.

select   

   tablespace_name, group_name

from   

   DBA_TABLESPACE_GROUPS;

TABLESPACE_NAME             GROUP_NAME

---------------                -----------

TEMP01                  TEMPGROUP_A

TEMP02                  TEMPGROUP_A

TEMP03                  TEMPGROUP_B

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  A single SQL operation by Scott can use more than one temporary tablespace for sorting.

     ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;