Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Sep 17 '13 at 10:52

This question came from our site for professional and enthusiast programmers.

    
If optimizing queries is not helping, then you would need to increase the size of undo tablespace –  Incognito Sep 17 '13 at 5:31
    
I don't have much knowledge of DBA can you tell me how can this be done? –  eatSleepCode Sep 17 '13 at 5:33
    
making the transaction scope shorter by doing a commit in between operations can help, if business allows this of course –  Joram Sep 17 '13 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. –  Incognito Sep 17 '13 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 Aldridge Sep 17 '13 at 6:28
add comment

1 Answer 1

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.

share|improve this answer
    
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. –  eatSleepCode Sep 17 '13 at 7:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.