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

Cause of "snapshot too old" error

I am querying the database to retrieve around 6,000 rows and processing them one by one. In the meantime I get the "snapshot too old" error. Can you please help me resolve this error and also tell me the root cause of this error?

I am querying the database to retrieve around 6,000 rows and processing them one by one. In the meantime I get the "snapshot too old" error. The error is as follows:
Exception chain (top to bottom):Start of Exception Chain
-------------------------------java.sql.SQLException: ORA-01555:
snapshot too old: rollback segment number 13 with name "_SYSSMU13$"
too small 0: oracle.xdb.XMLType.getDOM(XMLType.java:1524)
Can you please help me out to resolve this error and also tell me the root cause of this error?

Note: No one else did an UPDATE of my database while my transaction was happening (my transaction is also read only). Thanks in advance.

Looking at your undo segment name, I conclude that your database is using Automated Undo Management (AUM). The ORA-1555 error is caused by one of two problems when using AUM. One, your UNDO_RETENTION database parameter is set too low. Two, your Undo Tablespace is too small.

To decide how big your UNDO_RETENTION parameter needs to be, issue the following query:

SELECT MAX(maxquerylen) FROM v$undostat;
Set your UNDO_RETENTION parameter to a value larger than this. Increasing your UNDO_RETENTION will increase the total used Undo size in your tablespace, so you may have to increase the size of your database.

To determine how big your Undo Tablespace must be, issue the following query:

SELECT MAX(undoblks) FROM v$undostat;
Multiply the value returned by your DB_BLOCK_SIZE. Your Undo Tablespace must be larger than this value.

Frequent monitoring of V$UNDOSTAT can help you size your Undo space adequately and will make those ORA-1555 errors disappear.

This was last published in September 2005

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close