Home » RDBMS Server » Server Administration » ORA-01555 (Oracle Enterprise Edition10gR2 / Solaris 10)
ORA-01555 [message #513040] Thu, 23 June 2011 07:02 Go to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Hi All,

We are running a query in one of our databases using the database the link. The query fails in the middle with "ORA-01555: snapshot too old". Not sure, about the database which this error message points to? Will it be the database we have logged in or it is the database where db link points ?

Please provide your inputs on this.

Regards,
Antony

Report message to a moderator

Re: ORA-01555 [message #513044 is a reply to message #513040] Thu, 23 June 2011 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most likely it means "do not commit inside a cursor loop".

Regards
Michel

[Edit: add "]

[Updated on: Thu, 23 June 2011 07:17]

Report message to a moderator

Re: ORA-01555 [message #513046 is a reply to message #513044] Thu, 23 June 2011 07:20 Go to previous messageGo to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Thanks for the response. But rightnow we cannot look for modifying the code, as it is in production. We will be working on that also. But we are thinking about increasing the undo tablespace or retention parameter. I am not sure about how undo tablespace works in the case of a db link. Can you please explain on that? So that I can take necessary action.

Report message to a moderator

Re: ORA-01555 [message #513050 is a reply to message #513046] Thu, 23 June 2011 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you commit you say "the undo data are no more useful you can reuse the space".
You can try to increase undo tablespace size and undo retention parameter and, above all, convert your undo tablespace to a guaranted one.

Regards
Michel

Report message to a moderator

Re: ORA-01555 [message #513051 is a reply to message #513050] Thu, 23 June 2011 07:34 Go to previous messageGo to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
I am sorry, I think, I am not putting the question correct. The error "ORA-01555: snapshot too old" received while running the query, is it referring to the database which we are logged in or is it the database pointed by the db link?

Report message to a moderator

Re: ORA-01555 [message #513055 is a reply to message #513051] Thu, 23 June 2011 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It may be one or the other one, it depends on the query.
Did you mean that ALL tables referenced in the query are remote?

Regards
Michel

Report message to a moderator

Re: ORA-01555 [message #513056 is a reply to message #513051] Thu, 23 June 2011 07:47 Go to previous messageGo to next message
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends on whether you are pulling data from both DB's or just the remote. In the case of the later then it will be refering to the remote. Otherwise it could be either.
Snapshot too old happens when your query is forced to recreate changed data as of the point in time the query was started from the undo and finds that the change vectors it needs are no longer there.
Changes to a remote DB table will only be in the remote DB's undo.


EDIT: typo

[Updated on: Thu, 23 June 2011 08:40]

Report message to a moderator

Re: ORA-01555 [message #513080 is a reply to message #513056] Thu, 23 June 2011 08:37 Go to previous message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
I got the answer. Thank you so much....

Report message to a moderator

Previous Topic: convert global index to local index
Next Topic: ORA-00600: internal error code, arguments: [qerixGetKey:optdesc]
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 02 07:49:33 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.