RE: Undo Usage and Read consistency - ORA-1555
Date: Tue, 14 Jul 2009 13:35:10 -0400
I think it's important to note that you get an ora-1555 error as result of a commit happening in another session/process. It's not your select causing the error, it's just reporting why it can't give a read consistent view of the data. So often (but not always) you have to work on the process that is actually changing the data in order to really resolve the ora-1555 error. Personally, I find the only thing worse than figuring out bind variable peeking problems is figuring out ora-1555 issues.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nancy Iles
Sent: Tuesday, July 14, 2009 11:36 AM
To: cicciuxdba_at_gmail.com; oracle-l_at_freelists.org Subject: RE: Undo Usage and Read consistency - ORA-1555
We have sporadic ora-1555 on an exceptionally simple statement that occurs frequently in the application. How can you analyze why this tiny, simple statement is causing an ORA-1555? The statement is:
SELECT RESV_NAME_ID , RESORT FROM RESERVATION_NAME WHERE CONFIRMATION_NO = :1
I believe that this causes a user session to hang. Our third party vendor says that it is because a user terminates their session improperly and that it is not an issue.
Any suggestions on how to analyze the cause and the system impact?