Temporary tablespace group in oracle database

 

In this tutorial we will learn what is a temporary tablespace group, how to create a temporary tablespace group,how to add existing temporary tablespace to temporary tablespace group , how to set a temporary tablespace group to default temporary tablespace.

What is temporary tablespace group:-

In the previous tutorial we have learned about temporary tablespace. If we have one temporary tablespace all users are working on that temporary tablespace for that there will be a chance to reduce the performance. If we have bound multiple temporary tablespaces in a group all users are assigned to that temporary tablespace group and they will get all temporary tablespaces to do the multiple sorting,joining operations for that the conflict can be reduced.

How to create a temporary tablespace group in oracle:-

There will be no specific command to create a temporary tablespace group. We can create a temporary tablespace group by using create temporary tablespace statement.

SQL>create temporary tablespace TEMP02 tempfile '/u01/app/oracle/oradata/orcl/temp03.dbf' size 20m tablespace group temp_group;

Now we have other temporary tablespace TEMP02 and a temporary tablespace group is attached to that temporary tablespace. To view the temporary tablespace group we will use dba_tablespace_groups data dictionary table.

How to add existing temporary tablespace to this group:-

We have three temporary tablespaces (TEMP,TEMP01 and TEMP02) and TEMP02 is attached to the temporary tablespace group. We want to attach another temporary tablespace named TEMP01 to this group so we can execute the below command.

SQL>alter tablespace TEMP01 tablespace group temp_group;

Now TEMP01 is the member of the temporary tablespace group.

How to set a temporary tablespace group to default temporary tablespace:-

Now default temporary tablespace is TEMP01 . We want to set default temporary tablespace temp_group (temporary tablespace group name). So we need to execute the below command.
SQL>alter database default temporary tablespace TEMP_GROUP;

In this tutorial we have learned what is a temporary tablespace group, how to create a temporary tablespace group, how to add existing temporary tablespace to this group, how to set a to default temporary tablespace.
Stay tune with us.

SHARE

Admin

• 
• 
• 
• 
•