Re: About Rollback Segments

Jorge A. San Martín wrote:

> I have a lot of doubts about rollback segments (RS), I work with
> Oracle Server 7.3.2.3, we have a table with a lot of records; 1,000,000
> daily aprox, which must be deleted, but the rollback segments excedes
> the MAXEXTENTS parameter. I have read the manual about RS, but I don't
> know what values are the bests for a RS for this task. Is there a way
> to disable the RS ?

The rollback segments are what enables Oracle to undo transactions you don't wish to commit so even if could disable them (which you can't) it would not be a very good idea. There are a number of ways to get round your problem e.g.

1. Increase the size (it is best to drop and create each one in turn so INITIAL can = NEXT) of each rollback segment so less extents are thrown.
2. Ensure MAXTENTS is set to its maximum value. This can actually be unlimited since 7.3.3
3. Create a dedicated tablespace containing one large rollback segment and use the statement; set transaction use rollback segment <large_one_created_above> to force the delete to use that one.
4. Re-write the delete statement to be a PL/SQL routine that commits every n records.

I personally would recommend a combination of 3+4 as the PL/SQL approach allows for greter flexibility, especially in terms of re-runability. At the minimum I would implement 3 as this sort of task cries out for a dedicated rollback segment away from day to day work.

Hope this helps,

Ian Received on Mon Dec 01 1997 - 00:00:00 CST