Re: why undotbs01.dbf too big

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Thu, 21 Aug 2008 06:22:19 -0700 (PDT)
Message-ID: <9a964767-2c5b-4fb3-b5a9-e54241d60499@d1g2000hsg.googlegroups.com>


On Aug 21, 4:58 am, martin_ian_le..._at_yahoo.com wrote:
> Hello,
> I have a 10G on solaris database that has chewed up 3.0 GB of disk
> space with undotbs01.dbf. This is unexpected and inconvenient cos the
> disk partition is now full.
> There is a gateway feeding lots of small 3KB rows into a table which
> is caught by a trigger that populates some other tables via a stored
> procedure (about 30GB of data in all). So why would 3GB of undo space
> be needed?
>
> There is also an application that runs quite complicated queries again
> the db but my assumption is that does not need undo space?
>
> Any suggestions on why undotbs01.dbf is sooo big? And how to shrink
> it back?
>
> Best Regards
>
> Tony

Do a desc on gv$undostat. That will tell you the sql_id (and query run time) of the longest running query during any given ten minute period over the last few days, as that is usually the culprit. You can also look at gv$transaction.

HTH, Steve Received on Thu Aug 21 2008 - 08:22:19 CDT

Original text of this message