Re: how to shrink undo tablespace on 10GR2?

From: Kellyn Pedersen <>
Date: Wed, 20 Oct 2010 07:19:36 -0700 (PDT)
Message-ID: <>

I know we've discussed this in the past on here, with a bit of humor, but I 
am..oh what was the phrase Brandon Allen used to describe the type....  "A Temp 
and Undo Nazi", I believe was the term... :)   

A quick indicator in my book of a poor performing process is the amount of temp 
and/or undo that is required to complete the task-  It's the "work hard instead 
of work smart" scenario, so someone better justify the ratio.  If it has to 
"swap to disk" vs. performing the task in memory or if the database has to 
extensvely "track" where you've been as you've "trodded" through the database, 
again, you must justify the need to work this hard.

I have a shell script that goes out and checks temp and undo usage with 
thresholds set in the script's env.  If the threshold is breached for that 
database at any given run, an email goes out to the DBA's and the developers who 
work in that environment with a report of who is using what and who will be the 
winner of "Kellyn's Wall of Shame" for the day.  It has been a great deterent 
and method to get the developers to recognize when they need to revisit logic in 
their code and find more efficient ways to complete a task.

I'm a little concerned that a couple of the developers appear proud that they've 
hit the threshold.   There is still one I know of that believes using large 
amounts of temp and undo means he's just doing a lot of 
complicated "cool" aggregations with the data.  He's also the one most often to 
have huge processes go "awry" though and has not yet grasped that the two go 
hand in hand.   I need to convince him that although we are talking "size 
matters", it's the geek gadget type of size matters-  when it's 100's of GB's of 
data in your result set, you had better be going for the smallest and sleekest 
possible, especially when you are dealing with the largest amount of data!

Kellyn Pedersen
Sr. Database Administrator
"Go away before I replace you with a very small and efficient shell script..."

From: Niall Litchfield <>
Sent: Wed, October 20, 2010 1:26:36 AM
Subject: Re: how to shrink undo tablespace on 10GR2?

It's one of the reasons why undo tablespaces should have datafiles set at a 
fixed size and some sort of sensible undo_retention configured. Still no-one 
seems to know how much undo they generate per time period anyway so perhaps 
that's just wishful thinking on my part.
On 20 Oct 2010 01:52, "Daniel W. Fink" <> wrote:
>This is one of the 'nice features' of automatic undo. Since you are no longer 
>able to manually drop undo segments (in fact, you can't drop undo segments 
>without dropping the whole undo tablespace), all it takes is for a database to 
>experience high undo activity and a segment to be automatically created with a 
>block near the 'tail end' of the file. Since you can't drop the segment and 
>deallocation is spotty, you get a file with a lot of free space, but none 
>reclaimable because of the segment w/the high water mark block. Oracle really 
>should allow for the dropping of undo segments in this case.
>As others have said, your only choice is to create a new undo tablespace and 
>drop the old one. 
>On 10/19/2010 08:43 AM, dba1 mcc wrote:
>> We have 10GR2 on Linux server.  I found one database "...

Received on Wed Oct 20 2010 - 09:19:36 CDT

Original text of this message