Multiple Temporary Tablespaces in Oracle 10g

The creation and use of temporary tablespaces has just
taken another leap in performance and manageability, which should make us all
re-think how we have created and assigned our users to temporary tablespaces.

The TEMPORARY Tablespace

When a users executes a SQL statement that requires sorting
or does some sort of database maintenance that requires the creation of
temporary segments, it is the temporary tablespace that assists in that sorting
or holding of those temporary segments. If we did not have an area designated
as "temporary", these operations and segments would have to cohabitate
with permanent segments. We realized quite a few releases back in Oracle that
this was not a good idea and that we need to separate the temporary segments
from the permanent segments. If we did not allow this, we were imposing undo
harm within our database and to the performance of other SQL statements
accessing and manipulating the segments in those tablespaces.

The Evolution of the TEMPORARY Tablespace

  1. Oracle
    allowed DBAs to create a normal tablespace and assign this to users to use as a
    temporary tablespace through the CREATE USER or ALTER USER commands. These
    tablespaces could hold temporary segments and permanent segments. Oracle’s
    position at that time was to advise everyone not to place permanent segments
    into these tablespaces that were also being used as TEMPORARY tablespaces for

  2. Oracle
    then implemented the notion of a true TEMPORARY tablespace. These TEMPORARY
    tablespaces were to be assigned to users at creation time and could only hold
    temporary segments. This was a great step in the right direction because now we
    could not place permanent objects into them and no longer could users be
    assigned permanent tablespaces as their temporary tablespaces.

  3. Oracle
    then went one step further and allowed us to create a DEFAULT TEMPORARY
    tablespace for the entire database. The benefit this gives is, if we forget to
    specify a temporary tablespace for the user, they will inherit the DEFAULT
    TEMPORARY tablespace as theirs.

  4. Now
    in Oracle 10g, Oracle lets us group temporary tablespaces together to gain a
    bit more flexibility that allows us to create multiple temporary tablespaces
    and to create tablespace groups and assign these tablespace groups to users.

Tablespace Groups

Oracle now has the concept of grouping multiple tablespaces
together in what they call a tablespace group. This allows you to create
multiple temporary tablespaces, assign these temporary tablespaces to a
tablespace group, and then use the tablespace group as the default temporary
tablespace for the database or user. The benefit this has is that a single SQL
statement or set of SQL statements may use more than the one temporary
tablespace you have created in the past and now span multiple tablespaces. This
has the following benefits if you create your temporary tablespaces and
tablespace groups properly.

  1. You
    can tailor user load to individual temporary tablespaces.

  2. You
    can allow large sort operations to span and consume multiple temporary
    tablespaces if needed.

  3. Finer
    granularity so you can distribute operations across temporary tablespaces.

  4. Reduced
    contention when multiple temporary tablespaces are defined.

The Life of a Tablespace Group

You can implicitly create a tablespace group during the
creation of a temporary tablespace with the CREATE TEMPORARY TABLESPACE command
and by specifying the TABLESPACE GROUP clause. There is no CREATE TABLESPACE
GROUP statement. Therefore, during the creation of a temporary tablespace
called GTEMP01 we can add this tablespace to a tablespace group called GTEMP by
using the following statement.


The tablespace group name must be an existing tablespace
group name or one that does not exists. In addition, it must not be the name of
an existing tablespace or you will get an error such as the following that notifies
you that the tablespace already exists.

SQL> /
ERROR at line 1:
ORA-01543: tablespace ‘GTEMP’ already exists

We can also switch a tablespace’s group, add a tablespace to
a group or create a new tablespace group by using the following statement.


If you wanted to remove a tablespace from a tablespace group
and not assign it to a new tablespace group, you would use the following
statement where the TABLESPACE GROUP clause is used but an empty string is used
for the group.


You cannot get rid of a tablespace group explicitly. In
order for a tablespace group to be implicitly removed from the database, you
need to alter those tablespaces to either use another tablespace group or alter
them not to use any tablespace groups by assigning them to an explicit


You can always take a look at what tablespace groups you
have defined by looking at the DBA_TABLESPACE_GROUPS view. Issue the following
SQL and you can see what we have already defined for our running example.

SQL> SELECT group_name, tablespace_name
FROM dba_tablespace_groups;

—————————— ——————-

Setting the Default Temporary Tablespace

Now that we have defined multiple tablespaces to be part of
a tablespace group called GTEMP, we can now use this tablespace group just as any
other tablespace. One use of a tablespace group can be for the default
temporary tablespace. If you wish to assign this new tablespace group as the
default temporary tablespace, just issue the following SQL. Now any user who
you have not given a temporary tablespace at user creation time will be
assigned this new tablespace group as their temporary tablespace.

Database altered.

The temporary tablespace within large organizations that
require many sort operations typically would experience high levels of
contention. It was then left up to the DBA to segregate sort operations by
users and create islands of temporary tablespaces so that those operations
would not contend for similar resources that are associated with a single
temporary tablespace. The problem with this is that the very large operations
would typically need independent large temporary tablespaces. This would lead
to wasted resources. With the addition of tablespace groups, Oracle now allows
us to group multiple temporary tablespaces together so that we have one more
choice in the usage patterns of these temporary tablespaces. We now need to ask
ourselves if an operation truly needs its own individual temporary tablespace
or the operation can span multiple temporary tablespaces and work without
contention with the other operations that are occurring. If so, we can consolidate,
reduce disk consumption, and possibly contention.


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles