Re: About Rollback Segments

Jorge A. San Martín wrote:

> Hi,
> 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 ?
>

another possible solution as suggested in some dba/tuning manuals is to set up a very large rollback segment dedicated to large transactions. This rbs must be brought online and the transaction forced to use it and then taken offline. Not all applications can be designed for this since special sys privs must be used to bring the rbs online. Once the transaction finishes (i.e. rollback or commit), take the rbs offline to prevent normal transcations from using it. NOTE : The Set Transaction command MUST be the first command in the transaction. Also, you can use 'WHEN SQLERROR' to detect if the rbs cannot be brought online and handle the exception.

Here is a simple example SQL command file :

Alter Rollback Segment Rbs_Huge Online;
Commit; -- or rollback to start a new transaction Set Transaction Use RollBack Segment Rbs_Huge; Delete From Huge_Table;
Commit;
Alter Rollback Segment Rbs_Huge Online;

phil

--
Philip S. Fielder                                   1132/117
Yell.Net: 719.535.7713  Fax.Net: 719.535.4187       F3-0520
V.Net: 622.7713         V.Fax.Net: 622.4187         MCI/COS