Guang, Which version are you using? I tested this on a 8.1.7.4 system and it works fine. The only difference is, I didn't supply the INITIAL and NEXT; they are unnecessary anyway.
create rollback segment arup2 storage(maxextents 4); select max_extents from dba_rollback_segs where segment_name = 'ARUP2' returns 4, as expected! Anyway, the other issue is about your decision to limit extension of rollback segments to contain what you term as "runaway transaction". A transaction does not own an rbs, rather an rbs contains several transactions. When a txn changes data, it places the pre-image in the rbs and if there is no space, then the rbs grows. Now, a long transaction may be killed since a rbs space was not found, but it can also happen to a small, legitimate txn that needs to store the pre-image, simply because the long txn has grown the rbs to the maxextents. So, how did it help? It stopped a desired txn. Another problem is the read consistency. Not just transactions, but even selects also need to read data from RBS. If a rollback segment cannot grow, oracle determines if there is a way it can get the RBS to be used again. If there is no active transaction, then the old space is reused; but if a long running query needs that old data, i.e. pre-image, it doesn't find it and you get the dreaded "ORA-1555 Snapshot too old" error. The likelihood increases if your RBS can't grow. So, that was my concern for artificially limiting the RBS extension. If you need to hal t abnormlly long transactions, use resource managaers, but not using MAXEXTENTS. Arup ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 07, 2003 2:08 PM > Hi, Arup: > > I created lmt "rbs" this way: > > CREATE TABLESPACE RBS > DATAFILE '/oracle/u02/oradata/YPD/rbs01.dbf' SIZE 2048M > EXTENT MANAGEMENT LOCAL > UNIFORM SIZE 2M; > > and then > > SQL> create rollback segment RBSTest1 storage(initial 2048K next 2048K) > tablespace rbs; > > Rollback segment created. > > SQL> create rollback segment RBSTest2 storage(initial 2048K next 2048K > MAXEXTENTS 300) tablespace rbs; > > Rollback segment created. > > SQL> select SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT, > 2 MIN_EXTENTS,MAX_EXTENTS > 3 from dba_rollback_segs > 4 where SEGMENT_NAME like '%TEST%'; > > SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS > ------------------------------ -------------- ----------- ----------- > MAX_EXTENTS > ----------- > RBSTEST1 4194304 2097152 1 > 32765 > > RBSTEST2 4194304 2097152 1 > 32765 > > > It shows that you can not set MAXEXTENTS of a rollback segment when it is > created in LMT. What I mean the "run away transaction" is a transaction > that keep using rollback segment until it uses up all it's extents. In DMT > case, we can set the MAXEXTENTS of all the rollback segments so that there > is no transaction that can use the whole tablespace. But in LMT, it seems a > "run away" transaction can "eat up" the whole rbs tablespace because there > is no MAXEXTENTS ( ie, MAXEXTENTS = unlimited). Does anyone know there is > somewhere in Oracle Doc that I can find the answer of my question? > > Thanks. > > Guang > > ---------------- > Date: Mon, 06 Jan 2003 18:10:08 -0500 > > Guang, > > You should use LMTs with UNFORM extent allocation of some size So create the > tablespaces and the rollback segments but not the INITIAL or NEXT. > > I am not sure what you meamn by runaway processes. If a transaction needs > rollback segment space, it will need to extend it. You can still specify > MAXEXTENTS to limit the number of extents. > > HTH > > Arup > > > _________________________________________________________________ > Add photos to your e-mail with MSN 8. Get 2 months FREE*. > http://join.msn.com/?page=features/featuredemail > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
