Temporary tablespace group

What We Mean By That ?

A temporary tablespace group consists of only temporary tablespace, and has the following properties:

• It contains one or more temporary tablespaces.
• It contains only temporary tablespace.
• 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. 

Benefits :

Temporary tablespace group has the following 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.
• It allows a single SQL operation to use multiple temporary tablespaces for sorting. 

How to Know How Much Group you have And Each temp assign to them ? 

select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

 How to Create One :

CREATE TEMPORARY TABLESPACE temp01
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
     TABLESPACE GROUP groupaA_temp;

Note : you can create temp tablespace without assign to any group .  

Example 

     CREATE TEMPORARY TABLESPACE temp04
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
     TABLESPACE GROUP ‘’;

     CREATE TEMPORARY TABLESPACE temp04
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G;

Using Alter with them 

Remove from the group : 

 ALTER TABLESPACE temp01 TABLESPACE GROUP ‘‘;

Assign to Group :

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Define Group for specific user :

     ALTER USER Osama TEMPORARY TABLESPACE tempgroup_A;

 Refecnce Link :
1-ORACLE BASE
2-DBA Kevlar

Enjoy
Osama Mustafa