Re: snapshot too old

From: Alvin Law <alaw_at_oracle.com>
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 channel
Received on Fri Mar 24 1995 - 17:40:17 CET

Original text of this message