Oracle-L: Re: Unsetting OPTIMAL in rollback segments

From: <Cherie_Machler_at_gelco.com>
Date: Wed, 15 May 2002 10:18:30 -0800
Message-ID: <F001.00461636.20020515101830@fatcity.com>

Rob,

Just what the Dr. ordered.

Many thanks,

Cherie

                                                                                                                 
                    Robert Pegram                                                                                
                    <pegramrg_at_yaho       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>     
                    o.com>               cc:                                                                     
                    Sent by:             Subject:     Re: Unsetting OPTIMAL in rollback segments                 
                    root_at_fatcity.c                                                                               
                    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.

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).




-- 
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).

Received on Wed May 15 2002 - 13:18:30 CDT