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.
- Cherie_Machler_at_gelco.com 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: Cherie_Machler_at_gelco.com
>
> 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: ListGuru_at_fatcity.com (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: pegramrg_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Wed May 15 2002 - 12:38:40 CDT
