Reducing rollback segment tablespace sizes
I have a client where rollback segments were set up RBS01-RBS05, all in a tablespace called RBS. The person who set it up set %INCREASE at 50%. Well, I shrank the segments to themselves down to less than 500MB total from 4.6GB! But in using ALTER DATABASE DATAFILE . . . RESIZE . . . it won't let me resize it at all (it has extents scattered throughout). Anyone know how I go about shrinking it? Do I have to create a new tablespace/datafile, then drop the old one? TIA, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Reducing rollback segment tablespace sizes
Michael Milligan wrote: I have a client where rollback segments were set up RBS01-RBS05, all in a tablespace called RBS. The person who set it up set %INCREASE at 50%. Well, I shrank the segments to themselves down to less than 500MB total from 4.6GB! But in using ALTER DATABASE DATAFILE . . . RESIZE . . . it won't let me resize it at all (it has extents scattered throughout). Anyone know how I go about shrinking it? Do I have to create a new tablespace/datafile, then drop the old one? TIA, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] Waow !!! A true, live Oracle 6 database ? AFAIK, PCTINCREASE has been hard-coded to 0 for rollback segments since the beginnings of Oracle 7. Concerning your question, it all depends on where your existing rollback segments are located in the datafile (look at dba_extents). All you have to do is drop and recreate each roolback segment in tirn. Now, if this really is an Oracle6 database, RESIZE doesn't work ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Reducing rollback segment tablespace sizes
Micheal, Could you shrink the rbs even smaller? This should work if the first two extents of each rbs are in the 'first' blocks of the datafile. If not, the best solution is to create new smaller ones as you drop the others and then resize. Regardless of the solution, do this during a slow time to reduce the chance of a 1555. Daniel Fink Michael Milligan wrote: I have a client where rollback segments were set up RBS01-RBS05, all in a tablespace called RBS. The person who set it up set %INCREASE at 50%. Well, I shrank the segments to themselves down to less than 500MB total from 4.6GB! But in using ALTER DATABASE DATAFILE . . . RESIZE . . . it won't let me resize it at all (it has extents scattered throughout). Anyone know how I go about shrinking it? Do I have to create a new tablespace/datafile, then drop the old one? TIA, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Reducing rollback segment tablespace sizes
The high water mark for the rollback segments is preventing you from shrinking the files. Use alter rollback segment SEGMENT_NAME storage ( optimal OPTIMAL_SIZE) to set an optimal size for the segment, then use 'alter rollback segment SEGMENT_NAME shrink' to shrink the rollback. You may want to unset the optimal size when done by using NULL for the optimal size. I haven't tried this on a rollback segment with the intent of shrinking the data files, so I'm not sure it will do the trick. In that case, you can either just create a new rollback tablespace with new rbs segments, and then drop the old ones, or just drop all but the first of the rollback segments, then try your shrink of the datafile, and recreate the rbs segments. If you create a new tablespace, keep in mind that there may be scripts ( backup, etc) with the tablespace name hardcoded in them. Bad form, but it happens. HTH Jared Michael Milligan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/17/2003 01:29 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Reducing rollback segment tablespace sizes I have a client where rollback segments were set up RBS01-RBS05, all in a tablespace called RBS. The person who set it up set %INCREASE at 50%. Well, I shrank the segments to themselves down to less than 500MB total from 4.6GB! But in using ALTER DATABASE DATAFILE . . . RESIZE . . . it won't let me resize it at all (it has extents scattered throughout). Anyone know how I go about shrinking it? Do I have to create a new tablespace/datafile, then drop the old one? TIA, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Reducing rollback segment tablespace sizes
Thanks for the info. It's 8.1.7.4, and it's not hard-coded. I changed it from 50% to 0%, but the damage had been done. Maybe you're just nostalgiac for the good old days of v6! I did set an optimal, and used shrink. That worked fine to shrink the segments, but they are still scattered throughout the tablespace, preventing me from resizing the datafile. Thanks, Mike -Original Message- Sent: Monday, November 17, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Michael Milligan wrote: I have a client where rollback segments were set up RBS01-RBS05, all in a tablespace called RBS. The person who set it up set %INCREASE at 50%. Well, I shrank the segments to themselves down to less than 500MB total from 4.6GB! But in using ALTER DATABASE DATAFILE . . . RESIZE . . . it won't let me resize it at all (it has extents scattered throughout). Anyone know how I go about shrinking it? Do I have to create a new tablespace/datafile, then drop the old one? TIA, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] Waow !!! A true, live Oracle 6 database ? AFAIK, PCTINCREASE has been hard-coded to 0 for rollback segments since the beginnings of Oracle 7. Concerning your question, it all depends on where your existing rollback segments are located in the datafile (look at dba_extents). All you have to do is drop and recreate each roolback segment in tirn. Now, if this really is an Oracle6 database, RESIZE doesn't work ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Reducing rollback segment tablespace sizes
Daniel, Thank you very much. I'll do what you suggested, on both counts. I'll try shrinking them more first, then create new ones if needed. Thanks again, Mike -Original Message- Sent: Monday, November 17, 2003 2:49 PM To: Multiple recipients of list ORACLE-L Micheal, Could you shrink the rbs even smaller? This should work if the first two extents of each rbs are in the 'first' blocks of the datafile. If not, the best solution is to create new smaller ones as you drop the others and then resize. Regardless of the solution, do this during a slow time to reduce the chance of a 1555. Daniel Fink Michael Milligan wrote: I have a client where rollback segments were set up RBS01-RBS05, all in a tablespace called RBS. The person who set it up set %INCREASE at 50%. Well, I shrank the segments to themselves down to less than 500MB total from 4.6GB! But in using ALTER DATABASE DATAFILE . . . RESIZE . . . it won't let me resize it at all (it has extents scattered throughout). Anyone know how I go about shrinking it? Do I have to create a new tablespace/datafile, then drop the old one? TIA, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
