Re: Help: Rollbacks Keep Blowing Up-v7.1.4

In article <3h0hvf$1vc3_at_news-s01.ny.us.ibm.net>, gsalem_at_ibm.net says...
>
>In <3grp5p$c4k_at_lucy.infi.net>, bhunger_at_richmond.infi.net (Bill Hungerford)
 write
>s:
>Newsgroups: comp.databases.oracle
>From: gsalem_at_ibm.net
>Subject: Re: Help: Rollbacks Keep Blowing Up-v7.1.4
>Reply-To: gsalem_at_ibm.net
>References: <3grp5p$c4k_at_lucy.infi.net>
 <Pine.SUN.3.91.950203145449.245B-100000_at_s
>eatimes>
>X-Newsreader: IBM NewsReader/2 v1.07
>Organization: Infoel S.A.
>>Multiple SQL scripts running on system are blowing up due to rollback
>>segments ("snapshot too old").
 

>>
>>Also, it appears that when these jobs run, the rollback segments aren't
 going into extents.
>>Any help on this headache will be *greatly appreciated.
 

>>Bill Hungerford
>>bhunger_at_richmond.infi.net
>>(804)288-8827

 Hi,
>Snapshot too old is the error you get when you are fetching from an open
cursor
and the cursor is no more valid (due to rollback segments filling). I used to
>have this problems a lot, then I found that the best solution is:
>1- to minimize the number of commits within a cursor loop,
>2- in PLSQL and C progs try to use a cursor that can be restarted in a way
> not to process the same records twice (e.g. using a flag or ordering
the

    records in a way to avoid this problem. 3- catch the error and restart the cursor (close it then reopen and loop).

There is no easy solution for it.
>hope this helps
>
>

-- 
Ian Hariott                     ihariott_at_caritas.ab.ca
The solution to your problem is as follows.  There are rules governing the 
use of ROLLBACK segments.
a) For long running transactions use large rollback segments

b) For short running transactions use small rollback segment so that they
can be cached in memory.

You should create a mix of large and small rollback segments, and allocate
rollbacks segments depending on the length of your transactions.

To solve your problem, create a large rollback segment (initial, and next 
about 200k-500k depending on the length of your transaction).  Active this
segment when you are about to run the transaction that crashes your system.
You can do this by shutting down your database, changing the line Rollback 
Segs in the initORA file to include your large rollback segment, and 
omitting some of the small ones from this line, and then restarting your 
database. The other way is to use the command SET TRANSACTION USE ROLLBACK
SEGMENT large_seg.  I recommend creating a larger rollback segment so that 
you can avoid dynamic extension problems which will slow down your 
processing.  Consult your ORACLE7 Administration guide and read the chapter
Managing Rollback segments.

peace