First, I'd check to see if your UNDO_RETENTION is set high enough. It may need to be set high. What constitutes a "high" value? That all depends on your system and its usage. You'll have to query V$UNDOSTAT. Pay particular attention to the SSOLDERRCNT column. If this column contains a non-zero value, which is will since you received the ORA-1555 error, then your UNDO_RETENTION parameter is not set high enough to support your application's operations. You can query other columns from V$UNDOSTAT to get an idea of how much undo space is actually being used. You may be surprised to find that while you are receiving ORA-1555 errors, you are not using a great deal of space in your UNDO tablespace. UNDOBLKS will tell you the number of blocks used in the time slice represented by the row in V$UNDOSTAT. Also check MAXQUERYLEN as it will give you an indicator of how big UNDO_RETENTION needs to be.
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.