Author: Mc Carty

Page: 1 2 3

A temporary tablespace group consists of only temporary tablespace. A temporary tablespace contains one or more temporary tablespaces. A temporary tablespace 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.

Benefits of temporary tablespace:

Temporary tablespace group has the following benefits

• A temporary tablespace allows multiple default temporary tablespaces to be specified at the database level.

• A temporary tablespace allows the user to use multiple temporary tablespaces in different sessions at the same time.

• A temporary tablespace allows a single SQL operation to use multiple temporary tablespaces for sorting.

Using a tablespace with a temporary tablespace group:

Using a tablespace with a temporary tablespace group will result in the below select statement. However if you use tablespace without a temporary tablespace group then it will not return the select statement below.

Select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

TABLESPACE_NAME GROUP_NAME
--------------- -----------
TEMP01 TEMPGROUP_A
TEMP02 TEMPGROUP_A
TEMP03 TEMPGROUP_B

Temporary Tablespace in the temporary Tablespace group:

You can create a temporary tablespace and implicitly add it to a temporary tablespace group by using below syntax.

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u02/oradata/temp01.dbs' SIZE 500M
TABLESPACE GROUP tempgroup_a;

Temporary tablespace without temporary tablespace group:

You can create a temporary tablespace without assigning it to a temporary tablespace group. Below two statements are exactly equivalent.

Remove a temporary tablespace:

You can remove a temporary tablespace from a temporary tablespace group.

ALTER TABLESPACE temp04 TABLESPACE GROUP ‘‘;

Adding temporary tablespace to a temporary tablespace group:

Below syntax adds a temporary tablespace to a temporary tablespace group.

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Assigning User:

Below syntax assigns a user to a default temporary tablespace group.

ALTER USER USER TEMPORARY TABLESPACE tempgroup_A;

In this case, user 'USER' will have multiple default temporary tablespaces. A single SQL operation by 'USER' can use more than one temporary tablespace for sorting.

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Tricky Password Features in Oracle 11g!!
   Oracle – SQL Guide for Parallel Stored Procedures
   Great Tips for Successful Oracle Block Recovery!
   DBA Tips for Listener Logging Problems!!