Temporary Tablespace Groups

Temporary tablespace groups are a new feature introduced in Oracle10g. A temporary tablespace group is a list of tablespaces and is implicitly created when the first temporary tablespace is created. Its members can only be temporary tablespaces.

You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

Group Creation

You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

SQL> CREATE TEMPORARY TABLESPACE temp_test_1
2 TEMPFILE '/oracle/oracle/oradata/orclpad/temp_test_1.tmp'
3 SIZE 100 M
4 TABLESPACE GROUP temp_group_1;

Tablespace created.

SQL>

If the group temp_group_1 did not already exist, it would be created at this time. Now we will create a temporary tablespace but will not add it to the group.

SQL> CREATE TEMPORARY TABLESPACE temp_test_2
2 TEMPFILE '/oracle/oracle/oradata/orclpad/temp_test_2.tmp'
3 SIZE 100 M
4 TABLESPACE GROUP '';

Tablespace created.

SQL>

Now we will alter this tablespace and add it to a group.

SQL> ALTER TABLESPACE temp_test_2
2 TABLESPACE GROUP temp_group_1;

Tablespace altered.

SQL>

To de-assign a temporary tablespace from a group, we issue an ALTER TABLESPACE command as so:

SQL> ALTER TABLESPACE temp_test_2
2 TABLESPACE GROUP '';

Tablespace altered.

SQL>

Assign Users to Temporary Tablespace Groups

In this example, we will assign the user SCOTT to the temporary tablespace group temp_group_1.

SQL> ALTER USER scott
2 TEMPORARY TABLESPACE temp_group_1;

User altered.

SQL>

Now when we query the DBA_USERS view to see SCOTT's default temporary tablespace, we will see that the group is his temporary tablespace now.

SQL> SELECT username, temporary_tablespace
2 FROM DBA_USERS
3 WHERE username = 'SCOTT';

USERNAME TEMPORARY_TABLESPACE
-------- ------------------------------
SCOTT TEMP_GROUP_1

SQL>

Data Dictionary Views

To view a temporary tablespace group and it smembers we can view the DBA_TABLESPACE_GROUPS data dictionary view.

SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;

GROUP_NAME TABLESPACE_NAME
------------ ------------------------------
TEMP_GROUP_1 TEMP_TEST_1
TEMP_GROUP_1 TEMP_TEST_2

SQL>

Advantages of Temporary Tablespace Groups

• Allows multiple default temporary tablespaces
• A single SQL operation can use muultiple temporary tablespaces for sorting
• Rather than have all temporary I/O go against a single temporary tablespace, the database can distribute that I/O load among all the temporary tablespaces in the group.
• If you perform an operation in parallel, child sessions in that parallel operation are able to use multiple tablespaces.