RE: Rollback Segment Problem

One wouldn't have to deal with rollback issues on every day basis if s/he read manuals in advance and knew how to setup rollbacks right. If rollback tablespace is locally managed with uniform extent size - cannot be any fragmentation. If dictionary managed - tablespace pctincrease should be 0, minimumextent should be used (if version 8+), extent sizes for all rollbacks should be the same - equal tablespace default sizes etc. and one will never have any fragmentation problems

Regards, Alex Hillman

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Babich ,
> Sergey
> Sent: Wednesday, February 13, 2002 3:00 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Rollback Segment Problem
>
>
> Exactly. It depends pretty much on the application, how often it commits
> transactions, segment sizes, of course, OLTP amount etc. You can't always
> wait for SMON to wake up and do its job. The point is COALESCING
> alone won't
> always help and you have to defrag. Another point is ONE thing is reading
> the manuals, and ANOTHER one is dealing with rollback issues on every day
> basis.
> Best regards to everyone,
> Sergey
>
> -----Original Message-----
> Sent: Wednesday, February 13, 2002 2:14 PM
> To: Multiple recipients of list ORACLE-L
>
> Thanks...even is not true completely...if you have seen responses
> of Sameer
> Sarkar, originator of this thread...resolved his problem after
> coalesing his
>
> rbs table_space...
>
> I have to dealt with this situation on one of our databases
> because of some
> codes which results in lot of shrinkage of rollback segment and
> we have to
> coalesce that rbs tablespace quit often to get
> scattered/fragmented extents
> in contitgous extents....
>
> Regards
> Rafiq
>
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Wed, 13 Feb 2002 10:28:58 -0800
>
> IN THIS CASE yes, but NOT BEFORE.... That was my point.
> Thanks,
> Best,
> Sergey
>
>
> -----Original Message-----
> Sent: Wednesday, February 13, 2002 12:54 PM
> To: Multiple recipients of list ORACLE-L
>
> In many cases you will have to take RB segments offline and drop
> them, then
> re-create
>
> ------Even in this case you have to coalesce rbs tablespace before
> recreating rollback segments using that particular tablespace to make all
> released extents as contigous...Besides it is a good practice to reorg
> rollback segments..
>
> Regards
> Rafiq
>
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Wed, 13 Feb 2002 08:58:47 -0800
>
> Hi, guys,
> Just my $0.02, coalescing free space will NEVER eliminate
> fragmentation. In
> many cases you will have to take RB segments offline and drop them, then
> re-create. In the interim period you might want to have a big rollback
> segment(or a few) in another tablespace.
> Best,
> Sergey
>
> -----Original Message-----
> Sent: Wednesday, February 13, 2002 11:03 AM
> To: Multiple recipients of list ORACLE-L
>
> Samir,
> It is not coalesced automatically at shutdown and startup. It is to be
> coalesced manually. It applies to any tablespace with pctincrease set as
> 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to
> 8.1.7...
>
> Regards
> Rafiq
>
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Wed, 13 Feb 2002 05:48:28 -0800
>
> Rafiq,
>
> Just a small question....r the rollback segment extents in the rollback
> segment tablespace de-allocated when the database is shut down ??
> In that case, does the fragmentation remain when the database is restarted
> or r the blocks coalesced automatically ??
>
> Regards,
> Samir
>
> Samir Sarkar
> Oracle DBA - Lennon Team
> SchlumbergerSema
> Email : samir.sarkar_at_nottingham.sema.slb.com
> samir.sarkar_at_sema.co.uk
> Phone : +44 (0) 115 - 957 6217
> EPABX : +44 (0) 115 - 957 6418 Ext. 76217
> Fax : +44 (0) 115 - 957 6018
>
>
> -----Original Message-----
> Sent: 12 February 2002 17:49
> To: Multiple recipients of list ORACLE-L
>
>
> Is your tablespace fragmented as it is not finding contingous extent ..
> TRy to coalesce your subject tablespace and try...
>
> you can use following script to check whether coalesing is
> required or not..
> If percent is < 100 then coalesce it..
>
> select substr(tablespace_name,1,10)TS_NAME,total_extents
> "Total_Extnts",extents_coalesced,round(percent_extents_coalesced,0)
> from dba_free_space_coalesced
> order by tablespace_name
> /
>
> HTH
> Regards
> Rafiq
>
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Tue, 12 Feb 2002 02:33:19 -0800
>
> All,
>
> The following is the pitiable scenario I am in and would really
> appreciate a
> bit of help :
>
> We were running a long running delete job which gave the following
> error
> :
>
> Oracle8 Enterprise Edition Release
> 8.0.5.2.1 - Production
> PL/SQL Release 8.0.5.2.0 - Production
> SQLWKS> EXECUTE
> PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000,
> '/home/sqribe/work/live', '2000AnnualDataDelete.log');
> ORA-01562: failed to extend rollback segment
> number 8
> ORA-01650: unable to extend rollback segment
> RSUNDB08 by 50 in tablespace RSUN425
> ORA-06512: at
> "PAS.PKG_PAS_ANNUALDATAMAINTENANCE", line 66
> ORA-06512: at line 2
>
>
> I don't understand why it ran out of rollback space. The rollback
> segment
> has a virtually unlimited number of extents, so the only explanation I can
> see is that this particular rollback segment expanded until it
> exceeded the
> space allocation for the whole tablespace RSUN425, which is
> 3,000M. However,
> Oracle Storage Manager shows only 200M of the tablespace used, and it also
> shows high water marks against each rollback segment, none of
> which exceeds
> 400M. This would suggest to me that none of the previous month's deletions
> exceeded 400M per month, so why should it fail on this particular one when
> it had nearly 3,000M available?
>
> Is there something I am not understanding about rollback segments ??
>
> Thanks and Regards,
> Samir
>
> Samir Sarkar
> Oracle DBA - Lennon Team
> SchlumbergerSema
> Email : samir.sarkar_at_nottingham.sema.slb.com
> samir.sarkar_at_sema.co.uk
> Phone : +44 (0) 115 - 957 6217
> EPABX : +44 (0) 115 - 957 6418 Ext. 76217
> Fax : +44 (0) 115 - 957 6018
>
>
>
> __________________________________________________________________
> _________
> This email is confidential and intended solely for the use of the
> individual to whom it is addressed. Any views or opinions presented are
> solely those of the author and do not necessarily represent those of
> SchlumbergerSema.
> If you are not the intended recipient, be advised that you have received
> this
> email in error and that any use, dissemination, forwarding, printing, or
> copying of this email is strictly prohibited.
>
> If you have received this email in error please notify the
> SchlumbergerSema
> Helpdesk by telephone on +44 (0) 121 627 5600.
> __________________________________________________________________
> _________
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: SARKAR, Samir
> INET: Samir.SARKAR_at_nottingham.sema.slb.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).
>
>
>
>
> MOHAMMAD RAFIQ
>
>
> _________________________________________________________________
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
> INET: rafiq9857_at_hotmail.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: SARKAR, Samir
> INET: Samir.SARKAR_at_nottingham.sema.slb.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).
>
>
>
>
> MOHAMMAD RAFIQ
>
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
> INET: rafiq9857_at_hotmail.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: Babich , Sergey
> INET: SBabich_at_handexmail.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).
>
>
>
>
> MOHAMMAD RAFIQ
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
> INET: rafiq9857_at_hotmail.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: Babich , Sergey
> INET: SBabich_at_handexmail.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).
>
>
>
>
> MOHAMMAD RAFIQ
>
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
> INET: rafiq9857_at_hotmail.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: Babich , Sergey
> INET: SBabich_at_handexmail.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: Alex Hillman
  INET: ahillman_at_erols.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).