Re: Damn 1555 Snapshot too old PROBLEM

In <96179.220248U38790_at_uicvm.uic.edu>, <U38790_at_uicvm.uic.edu> writes:
>Fellow netters:
>
>We have an ORACLE database running on a SUN box. The end users run
>decision support type queries that take a long time to run, sometimes
>lasting upto 30 hours. During these periods of times, we run load jobs that
>load data into tables that the users may be querying. They may do updates,
>inserts , and some deletes.
>As a result, the queries bomb out with the wretched ORA-1555 error.
>Snapshot too old, Rollback segment too small.
>ORACLE support asked us to increase the size of the rollback segments.
>
>Is there any way that we can make this error extremely unlikely to occur?
>
>Can we have a dedicated rollback segment for each load job? Can we
>make each of these rollback segments quite large so that the rollback
>information stays in the rollback segment for a long time?
>
>I would like to give higher priority to user queries rather than load jobs
>because it it imperative that the queries complete rather than load jobs.
>Is this possible in any way?
>
>Thank you for your help. Any help regarding this 1555 problem is highly
>appreciated.

Dedicated rollback segments are obtained via the statement

set transaction use rollback segment seg_name; This must be the first line in a transaction, the statement will need to be re-issued after a rollback or commit

unfortunately this does NOT stop the other users from using your dedicated RBS, so it may be worth increasing the number of RBS's so the chances for this to happen is less likely

Bye,
Steve

opinions expressed are mine and do not
necessarily represent those of my employer Received on Fri Jun 28 1996 - 00:00:00 CEST