Re: Problem with rollback segments - HELP ??

In article <37830A67.8500BC61_at_ford.com>,   Haroon Khalique <hkhaliqu_at_ford.com> wrote:
> I am getting the 'ORA-01555: snapshot too old: rollback segment # with
> name "MPS_RS#" too small'
>
> The problem occurs whilst transfering data from the load table to the
> live table. Specifically it occurs only on the migration machine not
the
> development or live machine (which use same PL/SQL code, why does it
not
> occur there), and it only occurs for the same procedure. The
> environment is Oracle 7.3.4.3 on Solaris 2.6
>
> The rollbacks have been created with an optimal size setting. Is it
> shrinking back to this, thereby loosing the segment data whilst
waiting
> for a long query?
>
> We have tried to increase the PCTINCREASE on the rollback segment but
> this has no effect.
>
> Any suggestions for tracing the cause of why this problem occurs, and
> perhaps why not on the other machines?
>
> Thanks
>
> Haroon
>

May I suggest you compare the size of your extent settings between instances to see if the one instance having difficulties rbs segments are defined smaller than the other instances. After selecting an extent size set all your rbs segments so that initial = next and minextents = 10 to 20. Then your optimal should be set to minextents X next worth of bytes. This should give you a larger total segment area which should be able to retain more data longer before overlaying.

If your process is commiting as you go then you could be overlaying the data you need yourself. Decreasing the frequency of commits may be beneficial in this case.

--
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/
Share what you know. Learn what you don't. Received on Wed Jul 07 1999 - 10:14:48 CDT