RE: Undo Tablespace question

  • From: "Ron Rogers" <RROGERS@xxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>, <John.Hallas@xxxxxxxxxxxxxxxxx>
  • Date: Fri, 17 Sep 2004 08:28:43 -0400

  In addition to what John supplied, be cautious of setting the
RETENTION period. If the time
is set to long and you perform a major data load the UND_TBS will grow
as needed. Once it has 
grown the only method I know of to shrink it is to create another
UNDO_TBS1 and change the 
parameter in the init/spfile to point to the new tablespace.

>>> "Hallas, John, Tech Dev" <John.Hallas@xxxxxxxxxxxxxxxxx> 09/17/2004
5:17:39 AM >>>

The transition from RBS to UNDO is pretty easy

I would suggest retaining the same size UNDO as you have RBS at the =
moment, especially if you have no special (longer) retention period =
The init.ora (or hopefully spfile) should be amended to remove the =
following lines

The following parameters should be added
UNDO_SUPRESS_ERROR=3DTRUE (to stop errors occuring if the commands =
referring to rollback segments are used (online/offline/use etc)
UNDO_TABLESPACE  (multiple UNDO tablespace can be created (as normal
T/S =
- use LMT) but only 1 can be 'active'

Drop the RBS tablespace once all sessions using it have been removed
(it =
can always be re-created if necessary)

Experiment with the flashback_query facility, you might not need it but
it is a pretty neat thing and well worth playing with.

The main view to use for monitoring information is V$UNDOSTAT where you
can determine the maximum query length and the number of undo blocks =
used amongst other things.

In my view this is one of the easiest of the 9i features to implement
and you should not have many problems



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Paul Vincent
Sent: Friday, September 17, 2004 9:16 AM
To: oracle-l@xxxxxxxxxxxxx 
Subject: Undo Tablespace question

Now that we've finally got some 9i databases, I'm quite keen to switch
from Manual to Automatic Undo Management. It all looks pretty =3D
straightforward, but I do have a couple of questions that the "How To"
guides don't really address...

1. If I don't have any requirement for long-term retention of Undo data
after transactions commit, so that the default retention of 900 seconds
is quite sufficient, then what multiplier should I use when calculating
the size of my Undo Tablespace relative to the size of the old RBS =3D
tablespace? Say I currently have 500Mb RBS tablespace, then should a
500Mb undo tablespace be adequate, or should I allow double (1Gb) or

2. What monitoring practices are advisable to check the adequacy of the
undo tablespace provision? Can anyone point me at any webpages with
articles about monitoring AUM activity, for instance?

3. Once I've switched from Manual to Auto, does the RBS tablespace then
become completely superfluous? Can it be dropped? And what of the =3D
"rollback_segments" initialisation parameter? Do I remove this from the
parameter file, or is it best left there "just in case"?

Before I go diving in, in cavalier manner, I always like to check for


Paul Vincent
University of Central England

Other related posts: