Oracle Scratchpad

July 14, 2010

Changing UNDO

Filed under: Infrastructure,Troubleshooting — Jonathan Lewis @ 5:37 pm BST Jul 14,2010

From time to time people run into problems with UNDO tablespaces that have grown much larger than expected (perhaps due to a rogue process doing far too much work) and refuse to shrink. The workaround is to create a new undo tablespace and switch the instance to use it – but even this simple procedure introduces a couple of surprises.

There’s a little demonstration and discussion of the operation at this URL.

Footnote 1: The example assumes you are using an spfile for your database; if you’re not, then don’t forget that changing the undo tablespace with an alter system command can work – but you might get a nasty surprise if you forget to update the pfile before you next restart the database.

Footnote 2: It is possible (despite occasional claims to the contrary) to resize the files in an undo tablespace with the standard command:

 alter database datafile {filename} resize  .... ;

However, given the unpredictable way in which undo segments can be allocated, then grow, shrink and get de-allocated, you have to be a little lucky to find a moment when there is free space at the end of the file that can be de-allocated in a resize operation.


  1. There is another issue regarding undo and that is around “autoextend on” for the datafiles of the UNDO (part of the unpredictable undo segment allocation).

    A large process will start and at the beginning of that process it (oracle) determines that it has only so much to play with in the undo – it doesn’t recognize the autoextend being turned on for those datafiles. So it determines the size of the extents on the smaller side and will soon reach maxextents even though the undo tablespace could grow further. There is no way to drop those undo segments (ie, as in the old style drop rollback segment) which forces you to change the undo and gets you into that scenario for which Jonathan provided the link.

    Comment by Rui — July 14, 2010 @ 7:12 pm BST Jul 14,2010 | Reply

    • Rui,

      That’s an interesting observation.

      The limit on number of extents I can understand, since I can imagine that Oracle doesn’t want to have a secondary extent map anywhere in the undo segment – so won’t create the undo segment header block as type “unlimited extents”; but I am a little surprised by the “small extent” comment.

      Since the undo tablespace uses system allocated extents so I would expect it to follow the pattern of 16 * 64KB extents, 63 * 1MB, and then switch to 8MB extents. The only thing I can think of (and it’s not completely convincing) is that if you have lots of segments which are all still fairly small and still have their 16 x 64KB extents intact, then a long running transaction may try to steal extents before it grows the data file – and finds that the extents it has to steal are all small.

      This sounds like something halfway between a bug and a enhancement request. – are there any references on Metalink to the behaviour ? Is it restricted to any particular versions ?

      Comment by Jonathan Lewis — July 14, 2010 @ 9:51 pm BST Jul 14,2010 | Reply

      • it was a combination of 2 things:

        1) it was Bug 6475502

        2) note id : 420525.1 (plus some discussions my colleagues had with others regarding this issue)

        that made us think of turning autoextend off in our undo tablespace. Our environment was a 4 node datawarehouse (70 terabytes at the time). Undo retention was set quite high (I seem to remember something like 24 hours but can’t be sure). RHEL4 update 3 on 64 bit Intel.

        We had batch processing that took some 36 hours before failing on that maxextents error.

        Comment by Rui — July 16, 2010 @ 3:58 pm BST Jul 16,2010 | Reply

  2. To remove the unpredictability when resizing, you can find out the minimum size your redo tablespace’s data files can shrink to beforehand.
    You can generate the resize command using the query on this page:

    Comment by Donat Callens — July 15, 2010 @ 2:16 pm BST Jul 15,2010 | Reply

  3. […] Lewis links to instructions and explanations on how to switch to a different UNDO tablespace. It is trickier than it sounds and Jonathan provided additional traps to watch out […]

    Pingback by Log Buffer #196, A Carnival of the Vanities for DBAs | The Pythian Blog — July 23, 2010 @ 9:09 pm BST Jul 23,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by