Oracle-L: Re: Reducing rollback segment tablespace sizes

From: <Jared.Still_at_radisys.com>
Date: Mon, 17 Nov 2003 13:49:34 -0800
Message-ID: <F001.005D6F44.20031117134934@fatcity.com>

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 <Michael.Milligan_at_ingenix.com> Sent by: ml-errors_at_fatcity.com
11/17/2003 01:29 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        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
michael.milligan_at_ingenix.com

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: Michael.Milligan_at_ingenix.com

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: 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.net
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------