Re: Managing Rollbacks

dr.o <bonizak_at_mb.sympatico.ca> wrote in artikel <01bc6fb3$76652320$541ebeb4_at_ISBONI.NORTHWEST.CA>...
> I'm looking for ideas on how to manage rollbacks during batch processing.
>
> This particular job, we call daily_load, builds a cursor based on a store
> number. The PL/SQL then processes any records for that store number it
> finds in the intermediate table. It then goes to the next store number
> commiting between stores. But while it's reading the intermediate table,
> other batchs jobs are inserting into the intermediate table. A column in
> the intermediate table rows indicates if the row has been processed. When
> the cursor is finished, the job rebuilds the cursor to pick up new
 records
> in the intermediate table. It continues processing until 6:30 am.
>

You are probably doing selects on large tables. This is the situation when a query opens a cursor, then loops through  

fetching, changing, and committing the records on the same table. In this scenerio, very often ORA-1555 can result.

Check out the Oracle FAQ, there is much info on 1555 errors.               
                                         

The only way to resolve this is to use ONE LARGE rollback seg, e.g. 500-1000Mbyte.
A procedure for batch processing at night:

1. Backup the database.
2. startup the db making the ONE LARGE rbs active (in init.ora)
3. Do the batch processing.
4. before the users logon in the morning, run a "alter rollback seg R01 ONLINE ;" script for all small rollback segs.