Temporary Tablespace Group

Oracle 10g introduces a new term called “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;


Multiple Temporary Tablespaces
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:

-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;

The tablespaces assigned to a group can be viewed using:

SELECT * FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2

2 rows selected.

Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:

-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;

-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;

A tablespace can be removed from a group using:

ALTER TABLESPACE temp2 TABLESPACE GROUP '';

SELECT * FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP

1 row selected.

There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.

-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';

-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;

no rows selected