TEMPORARY TABLESPACE GROUPS

Hi,

Just in addition to the last post I would like to share this post as well.

TEMP tablespaces can be added into groups called the TEMPORARY TABLESPACE GROUPS

You can create multiple temporary tablespaces and can add it to temporary tablespace groups.

Then assign these temporary groups as default temporary tablespace to users.

The temporary tablespace groups are logical groups, but the temporary tablespaces which are part of the groups are physical in nature

The advantages of having temporary tablespaces are

* User session can use multiple temporary tablespaces for sorting

* We can set multiple default temporary tablespaces for users

Below examples is considering the database is a two node RAC.

Let us see how we can implement the TEMPORARY TABLESPACE GROUPS.

---------------------------------------------------------------

In database we can see that few users may do a lot of sorting operations and require more of temporary tablespace and few users may not.

If we have single temporary tablespace then if a particular user uses majority of temp tablespace then others will face a crunch for it.

Hence we can create multiple temporary tablespaces with varied sizes and add them to groups and assign the groups based on the usage as default tablespaces to users.

As example, we can create three temporary tablespaces

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

--------------- -------------

TEMP1 25

TEMP2 25

TEMP3 15

We can add TEMP1 and TEMP2 to a group called TEMP_A. This group can be assigned to all the users that use majority of the temporary tablespaces

We can add TEMP3 to a group called TEMP_B and assign it all other users

SQL> alter tablespace TEMP1 tablespace groups TEMP_A;

Tablespace altered

SQL> alter tablespace TEMP2 tablespace groups TEMP_A;

Tablespace altered

SQL> alter tablespace TEMP3 tablespace groups TEMP_B;

Tablespace altered

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

------------- -------------------

TEMP_A TEMP1

TEMP_A TEMP2

TEMP_B TEMP3

SQL> alter user XXA default temporary tablespace TEMP_A;

The advantage of this is that the user XXA may be running multiple sorting operations at any given point of time. By setting the temporary tablespace

group, the XXA can use TEMP1 or TEMP2 for the operations instead of one single temporary tablespace for the sorting operations.

Also you can set default temporary tablespace at database level to TEMP_B, so that any new users created will use TEMP_B as default temporary tablespace

SQL> alter database set default temporary tablespace TEMP_B;

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ --------------- -------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP_B Name of default temporary tablespace

-- Tanveer Madan