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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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 : [EMAIL PROTECTED] > [EMAIL PROTECTED] > 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: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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 : [EMAIL PROTECTED] > [EMAIL PROTECTED] > 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: [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). > > > > > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: SARKAR, Samir > 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). > > > > > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Babich , Sergey > 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). > > > > > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Babich , Sergey > 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). > > > > > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Babich , Sergey > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman 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).
