Re: ora err 1555

In article <8qd34f$ljv$1_at_porthos.nl.uu.net>,   "Frank" <frankbo_at_interaccess.nl> wrote:
> Nope, just for the one transaction (that would be oracle,
> not human transaction).
> Re-issue it.
>
> BTW, are you closing cursors, or doing commits over open cursors?
> Common cause for 1555's with procedural languages.
> --
> Frank
> Richard Leffingwell <rdlpip_at_mindspring.com> schreef in berichtnieuws
> 39c92e9a.14554091_at_news.mindspring.com...
> > Hello,
> >
> > I'm running a C program with embedded SQL that is constantly bombing
> > out with the error message ora-1555, refering that the 'snapshot is
> > too old, rollback segment too small'. The manual says to use a
 larger
> > rollback segment. Is this all I can do? And if I do get a larger
> > segment from my dba, how do I specify to use it. The manual says to
> > use the SET TRANSACTION statement, but where do I put this
 statement.
> > If I put it at the begining of my procedure with my SQL calls, will
> > this directive be valid for all the following SQL statements. Any
> > advice would be much appreciated.
> >
> > Thanks,
> > -rleffin
> >

The error message 'rollback segment too small' is usually wrong. You can get this error when the rollback segment is bigger than the table you are updating.

Oracle support has a white paper that does of good job of describing the several situations that commonly cause this error:

1. reading and updating more than a few rows in the same table
2. a long running transaction against a table being heavily updated by multiple sessions etc...

And possible solutions.

In case 1 using a driving table or pl/sql to control the updates can greatly help and in case 2 tunning your application to run faster can sometimes provide relief.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.