I am getting this error as a result of performing Insert and Select operation which involves calling of number of pl/sql procedures and functions. I am not getting exact reason. I tried optimizing queries I reduced fetch count of rows as well as reduces Inserting number of rows. What is the exact reason for this?
migrated from stackoverflow.com Sep 17 '13 at 10:52
This question came from our site for professional and enthusiast programmers.
UNDO is the area that stores the change vectors required to restore the data to the state that it was in at the beginning of the transaction.
The only cures for this are to reduce the amount of data that you're changing (by changing less data or reducing the transaction size), or to increase the available undo size.
Reducing the transaction size, which you'd do by committing more frequently, is problematic because you still have to deal with rolling back transactions that you've committed, and because it would also promote snapshot too old errors when the database cannot reconstruct from undo the data required for consistency with the start of a query.
So, as commenters say, the answer really is to have the DBA's adjust the size of UNDO available. Strangely, they can be reluctant to do so, or at least to do so adequately. If you currently have 256MB of UNDO and you ask for 2GB, they might see this as some huge amount that might in some way be undesirable and offer you 1GB. Smile indulgently at them, because you already doubled the amount that you really wanted and because there is no such thing as oversizing your UNDO tablespace.