Temporary Tablespace Groups

We covered temporary tablespaces in one of our blog post before.  In 10g, Oracle introduced the concept of tablespace groups for temporary tablespaces. We can assign more than one temporary tablespace to tablespace groups. What is the use of tablespace groups? It allows us to use temporary tablespace from a pool of multiple tablespaces. It is very useful in case of parallel operations where multiple tablespaces can be used. Also it is also useful in eliminating issues where tablespace is too small to hold the sort data.  Tablespace group is created when a first tablespace is assigned to the group. There is no explicit CREATE TABLESPACE GROUP command to create tablespace groups.  Oracle provides dba_Tablespace_groups data dictionary view to check the tablespace groups. There are certain rules in creating a tablespace group:

•    Group name cannot be same as tablespace name.
•    There should be at least one tablepsace in group.
•    Tablespace group cannot be empty. Group gets dropped when last tablspace from the group is dropped.
•    User can be assigned to tablespace group.

Following is an example of how to create tablespace groups. Let us start with creating a tablespace first and we will then assign it to a tablespace group.

SQL> CREATE TEMPORARY TABLESPACE TEMP_TB
2  TEMPFILE ‘C:\oracle\product\11.1.0\oradata\TEMP_TB.DBF’ SIZE 5M
3  tablespace group TE_GRP
4    /

We will create another temporary tablespace but without assigning it to the group.

SQL> CREATE TEMPORARY TABLESPACE TEMP_02
2  TEMPFILE ‘C:\oracle\product\11.1.0\oradata\TEMP_02.DBF’ SIZE 5M
3    /
4

Let us verify the group creation by querying dba_tablespace_groups view.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
—————————— ——————————
TE_GRP                         TEMP_TB

We can see here that tablespace temp_tb is assigned to group TE_GRP. Since we didn’t assign group when we created tablespace temp_02, it does not exist in the view. Let us alter the tablespace to add it to the group.

SQL> ALTER TABLESPACE temp_02 TABLESPACE GROUP TE_GRP;

Tablespace altered.

Querying dba_tablespace_groups will show now both tablespaces assigned to the group.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
—————————— ——————————
TE_GRP                         TEMP_TB
TE_GRP                         TEMP_02

We can also assign temporary tablespace groups to the user.

SQL> create user decipher identified by decipher
2  temporary tablespace te_grp;

User created.

Once we drop both the temporary tablespaces, tablespace group also drops automatically.

SQL> drop tablespace temp_02;
SQL> drop tablespace temp_tb;
SQL> select * from dba_tablespace_groups;

no rows selected

As mentioned earlier, tablespace groups make it possible to use multiple tablespaces for larger sort operations.

Resources:

• Chris foot article – here.
• Book: Oracle 10g Performance Tips and Techniques by Richard Niemiec

About these ads