Oralce - Temporary Tablespace Group

Temporary Tablespace Groups in Oracle

Oracle 10g introduced the concept of temporary tablespace groups.
This allows grouping multiple temporary tablespaces into a single group and assigning to a user, this group of tablespaces instead of a single temporary tablespace.
A tablespace group lets you assign multiple temporary tablespaces to a single user and increases the addressability of temporary tablespaces.

A temporary tablespace group has the following properties:

• It contains one or more temporary tablespaces (there is no upper limit).
• It contains only temporary tablespaces.
• 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.

There is no CREATE TABLESPACE GROUP statement, as implicitly created during the creation of a temporary tablespace with the CREATE TEMPORARY TABLESPACE command and by specifying the TABLESPACE GROUP clause.

Temporary Tablespace Group 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.
• Reduced contention when multiple temporary tablespaces are defined.
• It allows a single SQL operation to use multiple temporary tablespaces for sorting.
• Finer granularity so you can distribute operations across temporary tablespaces.

The following statement creates temporary tablespace temp as a member of the temp_grp tablespace group. If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement.

SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON TABLESPACE GROUP temp_grp;

Adding a temporary tablespace to temporary tablespace group:

SQL> ALTER TEMPORARY TABLESPACE temp TABLESPACE GROUP temp_grp;

Removing a temporary tablespace from temporary tablespace group:

SQL> ALTER TEMPORARY TABLESPACE temp TABLESPACE GROUP '';

Assigning temporary tablespace group to a user (same as assigning temporary tablespace to a user):

SQL> ALTER USER scott TEMPORARY TABLESPACE temp_grp;

Assigning temporary tablespace group as default temporary tablespace:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;

To see the tablespaces in the temporary tablespace group:

SQL> select * from DBA_TABLESPACE_GROUPS;

Temporary Tablespace Group related Oracle Views:

DBA_TABLESPACE_GROUPS

DBA_TEMP_FILESV$TEMPFILE
V$TEMPSTATV$TEMP_SPACE_HEADER

V$TEMPSEG_USAGE

Other Oracle articles:  Temporary Tablespaces in Oracle database   Wait events in Oracle Database