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?
-
If optimizing queries is not helping, then you would need to increase the size of undo tablespace– IncognitoCommented Sep 17, 2013 at 5:31
-
I don't have much knowledge of DBA can you tell me how can this be done?– eatSleepCodeCommented Sep 17, 2013 at 5:33
-
making the transaction scope shorter by doing a commit in between operations can help, if business allows this of course– JoramCommented Sep 17, 2013 at 5:34
-
You, as a developer(assuming that), might not have rights to extend tablespace yourself. You would have to check with your DBAs.– IncognitoCommented Sep 17, 2013 at 5:56
-
1@Joram commiting between operations is likely to promote a "snapshot-too-old" error and slow the process down. Better to size the undo correctly.– David AldridgeCommented Sep 17, 2013 at 6:28
Add a comment
|
1 Answer
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.
-
Thanks for the answer! I checked size for UNDO tablespace in dba_data_files and it seems user_bytes and user_blocks are almost equal to max_bytes and max_blocks.– eatSleepCodeCommented Sep 17, 2013 at 7:20