Multiple Temporary Tablespaces: Using Tablespace Groups

You can create a temporary tablespace group that can be specifically assigned to users in the same way that a single temporary tablespace is assigned. A tablespace group can also be specified as the default temporary tablespace for the database.


A tablespace group has the following characteristics:

1. It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.

2. It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.

3. 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.


How to create temporary tablespace group

Note: 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> ALTER TABLESPACE temp TABLESPACE GROUP group1;

Tablespace altered.

SQL> CREATE TEMPORARY TABLESPACE temp1
2 TEMPFILE 'c:\oracle\product\10.1.0\oradata\temp02.dbf' size 5m
3 TABLESPACE GROUP group2;

Tablespace created.

SQL> desc dba_tablespace_groups;
Name Null? Type
----------------------------------------- -------- ----------------------------

GROUP_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME NOT NULL VARCHAR2(30)

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1


How to change TABLESPACE GROUP


SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1

SQL> ALTER TABLESPACE temp1 TABLESPACE GROUP group1;

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP1 TEMP1


How to assign TABLESPACE GROUP to particular user.


SQL> select temporary_tablespace
2 from dba_users
3 where username = 'SCOTT';

TEMPORARY_TABLESPACE
------------------------------
GROUP1

SQL> alter user scott temporary tablespace group2;


How to delete temporary tablespace groups


SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1

SQL> alter tablespace temp1 tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP


How to Assigning a Tablespace Group as the Default Temporary Tablespace


SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP1 TEMP1

SQL> alter database ORCL default temporary tablespace group1;

Database altered.


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.