Re: undotbs01 growing , filling up filesystem

<<In a lot of cases this problem can be fixed by tuning or  redesigning the long running process>.

Thanks for responding. I agree. Any idea how I can identify the SQL or package that is to blame?

JL

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3ff16687$0$13355$ed9e5944_at_reading.news.pipex.net>...
> "John Leslie" <johnleslie_at_madasafish.com> wrote in message
> news:4412dd31.0312300310.5af61028_at_posting.google.com...
> > I have recently undertaken a default install of 9.2 Standard Engine.
> >
> > I notice that the undotbs01.dbf file just keeps on growing.
> >
> > Can I limit its size somehow, and if so what are the implications?
>
> Those lovely 'defaults' - though I suspect in this case that it is the
> default in the database creation assistant that has hit you. You have
> autextent set on thedatafile that is growing, the fix is either to turn off
> autoextend or to leave it on and set a maximum size for the file. If you
> look for autoextend at http://tahiti.oracle.com then you should find details
> of what is happening and how to change the behaviour.
>
> There are two potential downsides to limiting the size of the undo
> tablespace in 9i that I can think of.
>
> 1. You may have problems with long running queries and ora-01555 errors.
> This means that transactions might abort if they run out of space in the
> undo tablespace. In a lot of cases this problem can be fixed by tuning or
> redesigning the long running process.
>
> 2. You will limit the amount of undo that you retain and therefore the
> amount of time you can go back to using flashback queries.
Received on Wed Dec 31 2003 - 02:50:10 CST