RE: URGENT - Can you coalesce a RBS tablespace?

I've never had any problems with OPTIMAL (not optimum) in 8.1.7.4 (on HP - not sure about Solaris). Keep in mind that OPTIMAL only shrinks the RBS when a subsequent transaction causes inspection of the extended RBS again - it doesn't "snap" back to it's optimal size as soon as a long transaction completes - here is a description from the 9i doc:  

When you create or alter a rollback segment, you can use the storage parameter OPTIMAL (which applies only to rollback segments) to specify the optimal size of the segment in bytes. If a transaction needs to continue writing rollback information from one extent to another extent in the rollback segment, Oracle compares the current size of the rollback segment to the segment's optimal size. If the rollback segment is larger than its optimal size, and if the extents immediately following the extent just filled are inactive, then Oracle deallocates consecutive nonactive extents from the rollback segment until the total size of the rollback segment is equal to or close to, but not less than, its optimal size. Oracle always frees the oldest inactive extents, as these are the least likely to be used by consistent reads.    

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mercadante, Thomas F (LABOR) Sent: Tuesday, September 06, 2005 10:30 AM To: Chirag DBA
Cc: jhenslee_at_wausauwindow.com; Oracle-L Freelists Subject: RE: URGENT - Can you coalesce a RBS tablespace?

Optimum doesn't work too well in 8.1.7.4 (like not at all)  

Shrinking them by hand is certainly not the long-term answer. Re-writing the offending sql is the long-term answer.  

From: Chirag DBA [mailto:chiragdba_at_gmail.com] Sent: Tuesday, September 06, 2005 1:16 PM To: Mercadante, Thomas F (LABOR)
Cc: jhenslee_at_wausauwindow.com; Oracle-L Freelists Subject: Re: URGENT - Can you coalesce a RBS tablespace?  

Try setting the OPTIMUM parameter, which will reduce the size of yr segment after finishing the transaction. That can help you to get through.  

But if the transactions are long, they can generate 1555 erros in yr trace files.  

Look for this too.  

Regards - Chirag  

On 9/6/05, Mercadante, Thomas F (LABOR) < Thomas.Mercadante_at_labor.state.ny.us> wrote:

Jeff,

Have you shrunk the existing rollback segments? You may have some that are huge in size. You can shrink them and this will free up space in the RBS tablespace.

Tom

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of Henslee, Jeff Sent: Tuesday, September 06, 2005 12:55 PM To: Oracle-L Freelists
Subject: URGENT - Can you coalesce a RBS tablespace?

We seem to be having troubles with some of our RBS segments. We are unable to create new segments or extend existing ones. This is an unusual pattern and we're trying to find a way to get people back working again without having to add a datafile to the RBS area (if at all possible) or restart the instance.

Options please?

Thank you.

Jeffrey C. Henslee (Chico)
Six Sigma Green Belt
Wausau Window and Wall Systems
(715) 846-3196
mailto: jhenslee_at_wausauwindow.com <mailto:jhenslee_at_wausauwindow.com>

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l  

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 06 2005 - 12:43:21 CDT