 Oracle
Tips by BurlesonOracle10g New Data Dictionary View
Oracle 10g introduces a new data dictionary view,
Remote 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
Remote 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;
| 
