Just tested this on 8.1.7.0. alter rollback segment rbs0 storage(optimal null);
Rob Pegram Oracle Certified DBA ------------------------------------ SQL> select segment_name, optsize 2 from dba_rollback_segs, v$rollstat 3 where usn=segment_id; SEGMENT_NAME OPTSIZE ------------------------------ ---------- SYSTEM RBS0 4194304 RBS1 4194304 RBS2 4194304 RBS3 4194304 RBS4 4194304 RBS5 4194304 RBS6 4194304 8 rows selected. SQL> alter rollback segment rbs0 storage(optimal null); Rollback segment altered. SQL> select segment_name, optsize 2 from dba_rollback_segs, v$rollstat 3 where usn=segment_id; SEGMENT_NAME OPTSIZE ------------------------------ ---------- SYSTEM RBS0 RBS1 4194304 RBS2 4194304 RBS3 4194304 RBS4 4194304 RBS5 4194304 RBS6 4194304 8 rows selected. --- [EMAIL PROTECTED] wrote: > > We are getting the following error in our alert log > for a database where we > are doing some unusually large transactions: > > Wed May 15 03:26:22 2002 > Failure to extend rollback segment 27 because of > 1581 condition > FULL status of rollback segment 27 set. > > > On Metalink I've found a couple of references to bug > 228441. Following is > one such reference. Sounds like one part of the > work-around is to unset > OPTIMAL which we do currently have set. I've > looked through the docs and > Metalink. I do see text for altering the OPTIMAL > value but I don't see > any reference to "unsetting" OPTIMAL. Is there a > way to unset optimal in > rollback segments that are currently online? Or do > I have to create a > bunch of new rollback segments from scratch without > OPTIMAL specified and > then roll them in and roll out the existing rollback > segments. > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > > > > > > From: Oracle, Tom Villane > > 21-Sep-01 21:20 > > Subject: Re : ORA-01581, but > > seem to have more than enough > > space > > > > > > > > Hi, > > > > The ORA-01581 is not normally > > seen in Oracle8. Bug 228441 has > > an explanation of why it can > > happen. > > "This can happen when we are > > trying to extend a rollback > > segment. We try to extend the > > rollback segment when we cannot > > wrap into the next extent and > > we are near the end of the > > current extent. When we try to > > allocate the new extent we > > generate undo, in addition, > > space management may generate > > undo to do a coalesce. If the > > amount of undo we generate > > cannot fit in the current > > extent we will get a 1581 as we > > are now trying to use the > > extent that we are trying to > > add." > > > > Suggestions for resolving the > > problems are to make the > > INITIAL EXTENTS a large number > > ( maybe even set minextents = > > maxextents), and unset OPTIMAL > > > > > > Regards > > Tom Villane > > Oracle Support Metalink Analyst > > > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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). __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Pegram INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).