Rollback datafile size
The datafile for our rollback segments gre huge because of an improper setting. Even setting optimal and shrink, etc., won't help because the segments are scattered. Do I have to create a new tablespace? Can I change the datafile a rollback segment writes to and then take the other offline? The datafile is 4.6 GB but the segments are small, so all that space is wasted. Any help would be appreciated. I'm trying to read up more, but have found nothing about what to do when shrink won't work. Thanks, 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: Rollback datafile size
There is no need to drop the datafiles, just recreate the segments in the current tablespace. Pick a slow time to do this as you will significantly increase the likelihood of 1555s. 1) Offline rbs1 2) Drop rbs1 3) create rbs1 4) Repeat steps 1 - 3 for all rbs2..rbsN. 5) When complete, resize datafiles appropriately. Don't go to small or you run the risk of transactions failing due to unable to extend errors. Daniel Fink Michael Milligan wrote: The datafile for our rollback segments gre huge because of an improper setting. Even setting optimal and shrink, etc., won't help because the segments are scattered. Do I have to create a new tablespace? Can I change the datafile a rollback segment writes to and then take the other offline? The datafile is 4.6 GB but the segments are small, so all that space is wasted. Any help would be appreciated. I'm trying to read up more, but have found nothing about what to do when shrink won't work. Thanks, 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: Rollback datafile size
Daniel, Thank-you very much for your clear answer. Very helpful. When a RBS is taken off-line, does it transfer the rollback information to another segment? Thanks, Mike -Original Message- Sent: Monday, December 22, 2003 1:44 PM To: Multiple recipients of list ORACLE-L There is no need to drop the datafiles, just recreate the segments in the current tablespace. Pick a slow time to do this as you will significantly increase the likelihood of 1555s. 1) Offline rbs1 2) Drop rbs1 3) create rbs1 4) Repeat steps 1 - 3 for all rbs2..rbsN. 5) When complete, resize datafiles appropriately. Don't go to small or you run the risk of transactions failing due to unable to extend errors. Daniel Fink Michael Milligan wrote: The datafile for our rollback segments gre huge because of an improper setting. Even setting optimal and shrink, etc., won't help because the segments are scattered. Do I have to create a new tablespace? Can I change the datafile a rollback segment writes to and then take the other offline? The datafile is 4.6 GB but the segments are small, so all that space is wasted. Any help would be appreciated. I'm trying to read up more, but have found nothing about what to do when shrink won't work. Thanks, 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).
Re: Rollback datafile size
Mike, When an RBS is taken offline, the undo information is not transfered to another segment, hence the need to perform these actions at a quiet time. When an rbs is taken offline, all transactions currently using the rbs are allowed to complete (either commit or rollback). As these transactions are processing, the status of the segment is 'Pending Offline'. Once the tx is complete, the status will change to 'Offline'. If a query is running and needs undo entries from an offline (including pending) rbs, it will fail with a 1555. Daniel Michael Milligan wrote: Daniel, Thank-you very much for your clear answer. Very helpful. When a RBS is taken off-line, does it transfer the rollback information to another segment? Thanks, Mike -- 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: Rollback datafile size
Daniel, Thank you again. That clears it up even more. I'll wait for a quiet time and then proceed. Mike -Original Message- Sent: Monday, December 22, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Mike, When an RBS is taken offline, the undo information is not transfered to another segment, hence the need to perform these actions at a quiet time. When an rbs is taken offline, all transactions currently using the rbs are allowed to complete (either commit or rollback). As these transactions are processing, the status of the segment is 'Pending Offline'. Once the tx is complete, the status will change to 'Offline'. If a query is running and needs undo entries from an offline (including pending) rbs, it will fail with a 1555. Daniel Michael Milligan wrote: Daniel, Thank-you very much for your clear answer. Very helpful. When a RBS is taken off-line, does it transfer the rollback information to another segment? Thanks, Mike -- 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).
