Re: why undotbs01.dbf too big

On Aug 21, 9:22 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Tony, as Steve suggested you need to size your undo tablespace based on v$undostat (also look at dba_undo_extents). Then as David suggested you should probably replace or rebuild the undo tablespace depending on what your disk situation looks like. You might need to define a very small new undo tablespace, switch to using it, drop and recreate the original undo tablespace if using a different disk for the undo tablespace is an issue. I will second the idea that you should always set a maximum size on all extendable database data files.

HTH -- Mark D Powell -- Received on Thu Aug 21 2008 - 10:09:34 CDT