Re: snapshot too old
Date: 24 Mar 95 17:40:17
Message-ID: <ALAW.95Mar24174017_at_ap226sun.oracle.com>
In article <3krua6$g0f_at_news-2.csn.net> derby_at_csn.net (Gary Smith) writes:
> Dan Weinman (dano_at_bluedevil.cv.COM) wrote:
> : I have a pro*c program that is used to update selected records in a large table.
> : After processing about 400,000 records the program aborts with the
> : ORA-01555 error. The program declares a cursor for the select statement
> : and then fetches 500 rows at a time using the cursor. It then has the
> : possibility of updating any or all of the rows depending on the values of
> : the columns selected. A commit is done after the update and another 500
> : rows is fetched. This will continue until the error occurs.
>
> I had a similar problem in Oracle V6.0.37, which I solved by using the
> "set transaction use rollback segment SEG_NAME"
> after each commit.
>
> I'm not sure why this works and just a commit doesn't, however I do know it
> solved the problem. If anyone here knows WHY it works, I'd sure be in-
> terested.
ORA-01555 means the rollback segment is not large enough to handle the size of your transactions (which is apparent from your 400,000 record update). You can only use 1 rollback segment per transaction. You can assign a transaction to use a hugh rollback segment by using your syntax. You can only assign a rollback segment in the beginning of the transaction, hence right after a commit/rollback.
Using a hugh rollback segment does not really solve the problem. It only delays the problem. Sooner or later you could have a transaction that outgrows your rbs, or you could have two processes fighting for the same rollback segments. The best approach is to re-architect the update logic to put in intermittent commits so the rollback segment size is under control.
-- "And this is all I have to say about that..." - F. Gump ___ (o o) +-oo0-\_/-0oo---------------------------------------------------------------+ | Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com | +---------------------------------------------------------------------------+ ORA-03113: end-of-file on communication channelReceived on Fri Mar 24 1995 - 17:40:17 CET