The Geek Diary

Beginners Guide to Oracle Temporary Tablespace Groups

by

Temporary Tablespaces

Temporary tablespaces contain data that persists only for the duration of a user’s session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users.

A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. The performance of temporary tablespaces is extremely critical when your application uses sort- and hash-intensive queries, which need to store transient data in the temporary tablespace.

After starting up an instance, the first statement that uses the temporary tablespace creates a sort segment, which is shared by all sort operations in the instance. When you shut down the database, the database releases this sort segment. You can query the V$SORT_SEGMENT view to review the allocation and deallocation of space to this sort segment. You can see who is currently using the sort segment by querying the V$SORT_USAGE view. Use the V$TEMPFILE and DBA_TEMP_FILES views to find out details about the tempfiles currently allocated to a temporary tablespace.

Temporary Tablespace Groups

Large transactions can sometimes run out of temporary space. Large sort jobs, especially those involving tables with many partitions, lead to heavy use of the temporary tablespaces, thus potentially leading to a performance issue. Oracle Database 10g introduced the concept of a temporary tablespace group, which allows a user to utilize multiple temporary tablespaces simultaneously in different sessions.

Here are some of the main characteristics of a temporary tablespace group:

  • A temporary tablespace group must consist of at least one tablespace. There is no explicit maximum number of tablespaces.
  • If you delete all members from a temporary tablespace group, the group is automatically deleted as well.
  • A temporary tablespace group has the same namespace as the temporary tablespaces that are part of the group.
  • The name of a temporary tablespace cannot be the same as the name of any tablespace group.
  • When you assign a temporary tablespace to a user, you can use the temporary tablespace group name instead of the actual temporary tablespace name.
  • You can also use the temporary tablespace group name when you assign the default temporary tablespace for the database.

Benefits of Temporary Tablespace Groups

Using a temporary tablespace group, rather than the usual single temporary tablespace, provides several benefits:

  • SQL queries are less likely to run out of sort space because the query can now simultaneously use several temporary tablespaces for sorting.
  • You can specify multiple default temporary tablespaces at the database level.
  • Parallel execution servers in a parallel operation will efficiently utilize multiple temporary tablespaces.
  • A single user can simultaneously use multiple temporary tablespaces in different sessions.

Creating a Temporary Tablespace Group

When you assign the first temporary tablespace to a tablespace group, you automatically create the temporary tablespace group. To create a tablespace group, simply specify the TABLESPACE GROUP clause in the CREATE TABLESPACE statement, as shown here:

SQL> CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/temp01_01.dbf' SIZE 500M TABLESPACE GROUP tmpgrp1;

The preceding SQL statement will create a new temporary tablespace, temp01, along with the new tablespace group named tmpgrp1. Oracle creates the new tablespace group because the key clause TABLESPACE GROUP was used while creating the new temporary tablespace. You can also create a temporary tablespace group by specifying the same TABLESPACE GROUP clause in an ALTER TABLESPACE command, as shown here:

SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp1

The preceding statement will cause Oracle to create a new group named tmpgrp1, since there was no prior temporary tablespace group with that name. If you specify a pair of quotes (”) for the tablespace group name, you are implicitly telling Oracle not to allocate that temporary tablespace to a tablespace group. Here is an example:

SQL> CREATE TEMPORARY TABLESPACE temp02 TEMPFILE '/temp02_01.dbf' SIZE 500M TABLESPACE GROUP '';

The preceding statement creates a temporary tablespace called temp02, which is a regular temporary tablespace and does not belong to a temporary tablespace group. If you completely omit the TABLESPACE GROUP clause, you will also create a regular temporary tablespace, which is not part of any temporary tablespace group:

SQL> CREATE TEMPORARY TABLESPACE temp03 TEMPFILE '/u01/oracle/oradata/temp03_01.dbf' SIZE 500M;

Adding a Tablespace to a Temporary Tablespace Group

As shown in the preceding section, you can add a temporary tablespace to a group by using the ALTER TABLESPACE command. You can also change which group a temporary tablespace belongs to by using the ALTER TABLESPACE command. For example, you can specify that the tablespace temp02 belongs to the tmpgrp2 group by issuing the following statement:

SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp2;

The database will create a new group with the name tmpgrp2 if there is no such group already.

Setting a Group as the Default Temporary Tablespace for the Database

You can use a temporary tablespace group as your default temporary tablespace for the database. If you issue the following statement, all users without a default tablespace can use any temporary tablespace in the tmpgrp1 group as their default temporary tablespaces:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmpgrp1;

The preceding ALTER DATABASE statement assigns all the tablespaces in tmpgrp1 as the default temporary tablespaces for the database.

Assigning Temporary Tablespace Groups When Creating and Altering Users

When you create new users, you can assign them to a temporary tablespace group instead of to the single temporary tablespace. Here is an example:

SQL> CREATE USER TEST IDENTIFIED BY 
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE tmpgrp1;

User created.
SQL>

Once you create a user, you can also use the ALTER USER statement to change the temporary tablespace group of the user. Here is a SQL statement that does this:

SQL> ALTER USER TEST TEMPORARY TABLESPACE tmpgrp2;

Viewing Temporary Tablespace Group Information

You can use the new DBA_TABLESPACE_GROUPS data dictionary view to query the temporary tablespace groups in your database. Here is a simple query on the view that shows the names of all tablespace groups:

SQL> SELECT group_name, tablespace_name FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
---------- ---------------
TMPGRP1    TEMP01

You can also use the DBA_USERS view to find out which temporary tablespaces or temporary tablespace groups are assigned to each user. Here is an example:

SQL> SELECT username, temporary_tablespace
FROM dba_users;

USERNAME TEMPORARY_TABLESPACE
-------- ---------------------
SYS      TEMP
SYSTEM   TEMP
TEST    TMPGRP2
Beginners Guide to Oracle Temporary Tablespaces

You May Also Like