Re: Limiting undo segments size

Amir,

No and Yes...

You cannot specifically limit the size of an undo segment. This is part of what is now 'Automatic'. If the transaction needs space and space is/can be made available, the segment will extend.

However, you can limit the amount of undo that a specified user can allocate. Using the resource manager, specify the undo_pool for the group. I have not used the resource manager in production, so research it carefully.

Of course, you can always use manual undo management and take care of all this yourself.

Regards,
Daniel Fink

"Hameed, Amir" <Amir.Hameed_at_xerox.com> wrote: Folks, Is there a way to put a limit on the undo segments' size so that they do not grow beyond a certain size; essentially a similar behavior as the manual rollback segments. We have a 30GB undo tablespace. A user ran a job in a way that she was not supposed to and that job extended a particular undo segment to around 29GB and filled up the entire undo tablespace and also start impacting other transactions. The job was cancelled and now the rollback is in progress and Oracle is rolling back the transaction very slowly. In the mean time we have added a few more Gigs of space to the UNDO tablespace to minimize impact on other transactions. The permanent fix would be to educate users on how to run this job properly (This is a standard Oracle code) and push Oracle for a permanent fix. But from an educational stand point, is there a way to limit the growth of undo segments?

Thanks
Amir

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 21 2006 - 11:46:40 CDT