Best/Only way to change RBS initial/minextents

From: Don Seiler <don_at_seiler.us>
Date: 2 Oct 2002 16:14:14 -0700
Message-ID: <94e86aed.0210021514.10b083eb@posting.google.com>

Hullo. Like many out there I'm plagued by the "snapshot too old" errors at the worst times. Other than creating a dedicated rollback segment for large transactions, the recommended course of action seems to be:

1. Increase the initial extent on rollback segments to a much larger value so the allocated rollback is there without having to extend.
2. Increase the minextents parameter on the rollback segment to achieve the same effect (probably better than 1 for concurrency)

OK I stole those from a Thomas Kyte post.

Anyway to my dismay it appears that neither of those can be done through ALTER ROLLBACK SEGMENT, and that I need to drop and re-create them.

Here are the stats on all the rollback segments:

Initial size:  512 KBytes
   Next size:  512 KBytes
Optimal size: 1024 KBytes
 Minimum num: 2
 Maximum num: 249

I have 25 rollback segments (R01-R25) that are all like this. They reside on the RBS tablespace which is 4 GB (2 2GB datafiles). The db_block_size is 4096.

Basically I planned to do this for each one:

ALTER ROLLBACK SEGMENT R01 OFFLINE;
DROP ROLLBACK SEGMENT R01;
CREATE ROLLBACK SEGMENT R01
    TABLESPACE RBS
    STORAGE (

        INITIAL $A
        NEXT    $B
        MINEXTENTS $C
        MAXEXTENTS $D

    );
ALTER ROLLBACK SEGMENT R01 ONLINE; I need to know what the best values for A, B, C, and D would be for my database. I read that OPTIMAL is bad so I left it out of my example code here. Or just let me know if I'm crazy and doing things the incredibly hard way.

Any help you guys can provide would be great. I inherited this system so please be gentle. Received on Wed Oct 02 2002 - 18:14:14 CDT