Problem solve Get help with specific problems with your technologies, process and projects.

Rebuild Undo to a larger extent?

I have an UNDO question. I am getting 01555 errors nightly on the same query. There is probably another job running, doing alot of updates to the same table (I haven't confirmed this yet). My UNDO tbs is 11G, my undo_retention is 8 hours (I am using auto undo_management). The undo_retention has been increased from 2 hours to 3 to 6, and now to 8 hours over the past couple weeks. The maximum maxquerylen in v$undostat is 3 hours. I look in v$rollstat and see that there are thousands of wraps and extends. The initial extent is the default 64k on the UNDO tbs. I personally think I need to rebuild UNDO to a larger initial extent. Please advise if this is the right direction to go and, if so, what size initial extent might be?. If not, what action would you take?
The typical response to the ORA-1555 errors with Automated Undo is to increase the value of UNDO_RETENTION. But it appears that you are still losing information. Check the UNXPSTEALCNT and NOSPACEERRCNT columns of V$UNDOSTAT as well. A non-zero value in any of these columns means that your UNDO tablespace is not large enough. You can rebuild your UNDO tablespace to be uniform extents of 1 MB each and see if that makes a difference. Additionally, you can increase the values of your TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT parameters. Automated Undo uses these two parameters to help in determining how to handle undo segments. Query V$RESOURCE_LIMIT to see if your maximum number of concurrent transactions is higher then the TRANSACTIONS parameter. Make sure that TRANSACTIONS is greater that what V$RESOURCE_LIMIT tells you.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.