By Mike Ault
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;
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;
Figure 3.1: Temporary
tablespace
