OraFAQ Forum: Server Administration » "Snapshot is too old" problem

Home » RDBMS Server » Server Administration » "Snapshot is too old" problem (Oracle8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

"Snapshot is too old" problem [message #360365]

Thu, 20 November 2008 12:27

registereduser
Messages: 52
Registered: June 2008
Location: Toronto

Member

It is caused by running procedures. The dataset is very large. I am petty sure procedures do not have problem. How to fix it?

Report message to a moderator

Re: "Snapshot is too old" problem [message #360367 is a reply to message #360365]

Thu, 20 November 2008 12:42

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

From the code you posted I say that indeed there is a problem with the procedures.

Report message to a moderator

Re: "Snapshot is too old" problem [message #360368 is a reply to message #360365]

Thu, 20 November 2008 12:45

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do not commit inside the loop.

Regards
Michel

[Updated on: Thu, 20 November 2008 12:45]

Report message to a moderator

Re: "Snapshot is too old" problem [message #360369 is a reply to message #360368]

Thu, 20 November 2008 12:59

registereduser
Messages: 52
Registered: June 2008
Location: Toronto

Member

Thanks a lot.

I have checked again. It could not be script problem because it has run many many times in many many machines.

The problem is related to the undo segment while reach "rollback".

Report message to a moderator

Re: "Snapshot is too old" problem [message #360370 is a reply to message #360369]

Thu, 20 November 2008 13:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

The problem is related to the undo segment while reach "rollback".

No, this is not possible.

Regards
Michel

Report message to a moderator

Re: "Snapshot is too old" problem [message #360371 is a reply to message #360370]

Thu, 20 November 2008 13:17

registereduser
Messages: 52
Registered: June 2008
Location: Toronto

Member

a guy in my company told me it could be caused by too many rowes processed by the procedures. It cause the undo segment too large to stay in the memory and eventually caused the snapshot too old problem.

he suggests me to cut the process into segments and commited more often.

I am trying this way now.

Report message to a moderator

Re: "Snapshot is too old" problem [message #360373 is a reply to message #360371]

Thu, 20 November 2008 13:39

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Reread my first post.

Regards
Michel

Report message to a moderator

Re: "Snapshot is too old" problem [message #360385 is a reply to message #360371]

Thu, 20 November 2008 15:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

registereduser

he suggests me to cut the process into segments and commited more often. I am trying this way now.

Sounds like a great way to be sure to run into the ORA-01555: snapshot too old error.

Report message to a moderator

Previous Topic:	Oracle 9i
Next Topic:	How to check partitioned table local indexes is valid

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 18:43:39 CST 2024