Re: undotbs01 growing , filling up filesystem

"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.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK