Managing Oracle’s Undo Tablespaces

In the days of yore, we had
rollback segments. In order to use a rollback segment in a transaction, either
by letting Oracle choose one or by explicitly setting/naming one, the rollback
segment first had to be specified in the initialization parameter file. With
the advent of Automatic Undo Management (AUM), Oracle abstracts the management
of undo space for you. It’s fairly common to see references to three
undo-related parameters: undo_management, undo_retention, and undo_tablespace.

How the database operates, that
is, manual versus automatic undo modes, is driven by the setting of the undo_management
parameter. How long Oracle will attempt to retain undo information is set by undo_retention,
with a default value of 900. An out of the box/DBCA-created name for the
tablespace is UNDOTBS1. The parameters and their values are shown below via

So, the question is this: if
using automatic undo, do you need to specify an undo tablespace? In other
words, because you had to specify the name of rollback segments somewhere (i.e.,
in the control file) for manual undo, are you required to specify the name of
the undo tablespace somewhere as well (i.e., in the init.ora file)? The answer
is no, you do not have to name the undo tablespace in the init.ora file. If you
don’t specify the name of the undo tablespace, how does Oracle know if you have
one to begin with?

Recall that the creation of
an undo tablespace starts with CREATE UNDO TABLESPACE <name>. As
you must have one and only one undo tablespace online at any given time when
using AUM (assuming the database is open), Oracle will still know which undo
tablespace is active for the database, and you don’t need to name/specify it in
the init.ora file.

Extract from the spfile

Given that you do not have
to specify the tablespace, what are the cases where you could or should do so?
One case is when you have more than one undo tablespace. Perhaps your database
starts with UNDOTBS1, and while open, you change over to UNDOTBS2. Upon
shutdown, which tablespace will be the undo tablespace of record upon the next
startup? A second case is for informational purposes. It doesn’t hurt to
specify the tablespace, and if other DBAs are involved, it removes the guessing
game aspect as to which undo tablespace is online/active. A third case is
perhaps the most important reason why you should always specify an undo

Let’s suppose you create a
second undo tablespace, naming it UNDOTBS2. When it is created, and recalling
that only one undo tablespace at a time is active, what is the status of the
newly created tablespace – online or offline?

If there is another undo tablespace, and it is not being
used as the current undo tablespace of record, then it doesn’t matter if that
not used undo tablespace is online or offline. What
is also shown is that it is necessary in and of itself that the in-use
designated undo tablespace be online. Put another way, being online is not
sufficient, but rather, it is necessary. This hints at the third case mentioned
above. However, if not in use, then the status does not matter.

Now that there are two undo tablespaces
to choose from, even with the undo_tablespace parameter blanked out, let’s
confirm that Oracle will remember which undo tablespace was last used prior to
shutdown and used on the next startup. The system is altered, but the scope
(for the server parameter file) is not specified.

The output clearly shows
that Oracle remembers what the current undo tablespace is, even if you don’t
specify it. What happens if you have undo_management set to auto, but no undo
tablespace specified? Are you forced or required to use a separately created
undo tablespace, or will the SYSTEM tablespace act as the default?

The first problematic area
is that you have to go out of your way to drop undo tablespaces. To drop a
tablespace in general, the database must be open. If open, there must be an
active undo tablespace. This almost seems like a catch-22 situation, but there
is an out. The solution is to unset the undo_tablespace parameter. Once unset
(set the value to ‘’), you can then drop it. Undo has to go somewhere, and that
place is now the SYSTEM tablespace. It should be blatantly obvious as to why
you would not want to use this tablespace as the undo repository. Since undo is
now in this tablespace, and coming back to the online versus offline
conditions, what is always true about the status of the system tablespace? That’s
right – the system tablespace can never be offline.

Now that all explicitly
created undo tablespaces are dropped and undo is being stored in system, what
happens when we create a new undo tablespace but don’t specify it for the
parameter? Will Oracle automatically look for an undo tablespace? Let’s create
a new undo tablespace (reusing the space/file from before)

Apparently, Oracle can’t
wait to offload undo onto an undo-specific tablespace. It doesn’t even wait for
you to set the parameter, that’s how eager it is to get undo out of SYSTEM.

In Closing

What are the object lessons demonstrated
about undo tablespaces? From Oracle’s perspective, they are:

  • If at least one undo tablespace
    exists, use it, even if the name isn’t specified.
  • If more than one exists, but none
    have been named, use the first one created.
  • If undo_tablespace is unset and
    an undo tablespace is in use immediately prior to shutting down, use that
    tablespace upon next startup.
  • If no undo tablespace exists at
    all, then use SYSTEM. As soon as an undo tablespace is created, immediately
    switch AUM duties over to the new tablespace.

Since you will always have
an online SYSTEM tablespace at your disposal, one way to get around running out
of disk space (because an undo tablespace was allowed to autoextend and all
space is used up on a drive or mount point, and space is limited elsewhere),
you can unset the undo_tablespace parameter, defaulting to SYSTEM for a moment,
then drop the oversized undo tablespace, and re-create it with a smaller/more
constrained size. Of course, if you have enough space elsewhere, create the new
undo tablespace there before dropping the current one. Add this one to your bag
of tricks; it may come in handy some day to dig your database out of a hole. It’s
not without a price though, because when dropped, the undo for however long
retention was set to is gone as well.


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles