Temporary Tablespaces Group

Large transactions can sometimes run out of temporary space. Large sort jobs, especially those involving tables with many partitions, lead to heavy use of the temporary tablespaces, thus potentially leading to a performance issue. Oracle Database 10g introduced the concept of a temporary tablespace group, which allows a user to utilize multiple temporary tablespaces simultaneously in different sessions.

Here are some of the main characteristics of a temporary tablespace group:

• A temporary tablespace group must consist of at least one tablespace. There is no explicit maximum number of tablespaces.
• If you delete all members from a temporary tablespace group, the group is automatically deleted as well.
• A temporary tablespace group has the same namespace as the temporary tablespaces that are part of the group.
• The name of a temporary tablespace cannot be the same as the name of any tablespace group.
• When you assign a temporary tablespace to a user, you can use the temporary tablespace group name instead of the actual temporary tablespace name.
  You can also use the temporary tablespace group name when you assign the default temporary tablespace for the database.

Creating a Temporary Tablespace Group

When you assign the first temporary tablespace to a tablespace group, you automatically create the temporary tablespace group. To create a tablespace group, simply specify the TABLESPACE GROUP clause in the CREATE TABLESPACE statement, as shown here:

SQL> CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/oracle/oradata/temp01_01.dbf' SIZE 500M TABLESPACE GROUP TEMP;

The preceding SQL statement will create a new temporary tablespace, temp01, along with the new tablespace group named tmpgrp1. Oracle creates the new tablespace group because the key clause TABLESPACE GROUP was used while creating the new temporary tablespace.
You can also create a temporary tablespace group by specifying the same

TABLESPACE GROUP clause in an ALTER TABLESPACE command, as shown here:

SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP TEMP

The preceding statement will cause Oracle to create a new group named TEMP, since there was no prior temporary tablespace group with that name. If you specify a pair of quotes ('') for the tablespace group name, you are implicitly telling Oracle not to allocate that temporary tablespace to a tablespace group. Here is an example:

SQL> CREATE TEMPORARY TABLESPACE temp02 TEMPFILE '/u01/oracle/oradata/temp02_01.dbf' SIZE 500M TABLESPACE GROUP '';

The preceding statement creates a temporary tablespace called temp02, which is a regular temporary tablespace and does not belong to a temporary tablespace group. If you completely omit the TABLESPACE GROUP clause, you will also create a regular temporary tablespace, which is not part of any temporary tablespace group:

SQL> CREATE TEMPORARY TABLESPACE temp03 TEMPFILE '/u01/oracle/oradata/temp03_01.dbf' SIZE 500M;

Verify Temp tablespace usage:-

SELECT   

   TABLESPACE_NAME, GROUP_NAME

FROM   

   DBA_TABLESPACE_GROUPS;

SELECT USERNAME, SESSION_NUM, TABLESPACE FROM V$SORT_USAGE;

Reference: 

Master Note: Overview of Oracle Temporary Tablespaces (Doc ID 1498442.1)

10g: Temporary Tablespaces Group (Doc ID 245645.1)

http://www.dba-oracle.com/t_temporary_tablespace_groups.htm