Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 23, 2008

Managing Oracle's Undo Tablespaces

By Steve Callan

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 SQL*Plus.

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 tablespace.

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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM