Re: How to clear UNDOTBS- Slightly OT

From: Kellyn Pedersen <>
Date: Mon, 18 Jan 2010 09:26:15 -0800 (PST)
Message-ID: <>

OK, I'm just going to ask this of the DBA's here.  I'm going to be perfectly honest here-  If I had a 100MB database with a 25GB undo tablespace, my ADHD, slightly obsessive compulsive, little DBA self would be going it bit crazy with that... :)  I do have an APEX system that uses a ton of undo...and I have put the challenge to the developer to correct the code that is utilizing that undo inefficiently, printing out the fixes that are needed to stop doing so- but I still have to look at a 10GB undo on a 35GB database, which annoys me, which means I get to tease and annoy him... :)

I was "raised" as a DBA, that when I had a large temp and undo tablespace in comparison to my database size, (and keep in mind, it's not actually size that is the issue here, it's the size utilized per transaction, but when it's huge overall, that's a pretty good indicator...) then I need to examine WHAT exactly is using them both.

I was taught that a developer and user is going to see temp and undo just like any other tablespace-  it runs out of space, you add more, but a DBA should NEVER see these two this way.  Tune your temp usage, verify that your undo is being utilized efficiently and you notice over all that your database is running efficiently as well.  Otherwise my database is just working hard and not working smart..  It means that less is working in memory, less is fitting in memory, less is using the right objects or taking the "long route" to what it needs in my database and more I/O which makes Kellyn a grumpy DBA... :)

How many DBA's inspect what is utilizing temp and undo?  How many DBA's see a large temp and/or UNDO tablespace as a red flag in a database? 

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.

"Go away before I replace you with a very small and efficient shell script..."

  • On Mon, 1/18/10, Roger Xu <> wrote:

From: Roger Xu <>
Subject: Re: How to clear UNDOTBS
Cc: "ORACLE-L" <> Date: Monday, January 18, 2010, 10:02 AM

I have an issue where the undo tablespace datafile is 25 GB in size with less than 100 MB is actually used but we need to backup the 25 GB datafile on every level 0 RMAN. My solution is to use RMAN compress on this datafle and it works great. Instead of 25GB, I am seeing some MB only. Thanks.

On Mon, Jan 18, 2010 at 7:26 AM, Eriovaldo Andrietta <> wrote:


Received on Mon Jan 18 2010 - 11:26:15 CST

Original text of this message