Re: why undotbs01.dbf too big

Comments embedded.
On Aug 21, 3: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.

Which is precisely why you shouldn't be using autoextend in an unrestricted fashion; I prefer to not use autoextend at all.

> 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?
>

Many reasons, a few being: large number of transactions, large transaction size, high undo_retention setting.

> There is also an application that runs quite complicated queries again
> the db but my assumption is that does not need undo space?
>

Why wouldn't it? You need a read consistent image of the data and many times that image is generated by using the UNDO generated by other transactions.

> Any suggestions on why  undotbs01.dbf is sooo big?

You set the file to autoextend.

>And how to shrink
> it back?

Create a new and smaller undo tablespace, dynamically change the init.ora parameter unto_tablespace to point to the new undo tablespace then, when you bounce the instance drop the old undo tablespace.

>
> Best Regards
>
> Tony

David Fitzjarrell Received on Thu Aug 21 2008 - 08:28:45 CDT