Managing Rollbacks

I'm looking for ideas on how to manage rollbacks during batch processing.

We are a retail company. We process store sales in batch at night. One of our batch jobs processes records from an intermediate table inserting them into other tables for sales reconciliation, among other things.

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.

The problem is that we never know how many records will be processed per cursor, and therefore have almost no idea of how big a rollback it will need at any time. Currently the rollback tablespace is at 1G, and it has ora-1555'd on us numerous times. We just made the tablespace 2G, but this method of hit-'n'-miss storage management makes us cringe. Normally the rollbacks sit at or near optimal when this job is not running.

I've cron'd some pl/sql scripts to report on the rollback sizes during batch, but I'm looking for some ideas on how others have managed their rollbacks during batch runs.

One idea I'm trying is to have the app designer set transaction to an incrementing rollback number. i.e evey reiterative transaction within the job's execution does a set transaction rollback rbs n+1. Then I cron an alter rollback segment rbs n shrink, cycling through all rbs' every 5 minutes.

What have you done?

-- 
bonizak_at_northwest.ca