Re: why undotbs01.dbf too big
Date: Thu, 21 Aug 2008 08:09:34 -0700 (PDT)
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.
> 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