Unsetting OPTIMAL in rollback segments
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
Re: Unsetting OPTIMAL in rollback segments
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
Re: Unsetting OPTIMAL in rollback segments
Rob, Just what the Dr. ordered. Many thanks, Cherie Robert Pegram pegramrg@yaho To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] o.com cc: Sent by: Subject: Re: Unsetting OPTIMAL in rollback segments [EMAIL PROTECTED] om 05/15/02 12:38 PM Please respond to ORACLE-L 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
