RE: ALTER ROLLBACK SEGMENT rbs SHRINK

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Wed, 30 Jan 2002 11:10:37 -0800
Message-ID: <F001.00400DD6.20020130110047@fatcity.com>

You are welcome, In our shop I have been using this command occasionally to get release some space back to tablespace...

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 30 Jan 2002 09:55:35 -0800

Yes, Mohammad is right !!

I messed up by not seeing the correct column...

Oracle will not honor the size you asked for (to shrink), if there are other transactions using up space more than what you asked for, but it can go below the OPTSIZE...

Thanks, Mohammad.

Kirti

-----Original Message-----
Sent: Wednesday, January 30, 2002 10:40 AM To: Multiple recipients of list ORACLE-L

Yes, it can be shrinked below optimal as per following details specially R03

rollback segment....
Regards
Rafiq

before:
NAME EXT MB OPTSIZE ACTIVE WATER SHRINKS WRAPS WAITS STATUS
--------------- ---- ------- ------- ------- ------- ------- -------- ------

SYSTEM 2 0.2 0.0 0.1 0 0 0 ONLINE
R04 57 115.8 200 0.0 115.8 0 0 0

ONLINE
R01 50 101.6 200 0.0 101.6 0 0 0

ONLINE
R02 50 101.6 200 0.0 101.6 0 0 0

ONLINE
R03 99 201.1 200 0.0 201.1 0 0 0

ONLINE

run script:
SQL> alter rollback segment r03 shrink to 100M;

After:

NAME EXT MB OPTSIZE ACTIVE WATER SHRINKS WRAPS WAITS STATUS
--------------- ---- ------- ------- ------- ------- ------- -------- ------

SYSTEM 2 0.2 0.0 0.1 0 0 0 ONLINE
R04 57 115.8 200 0.0 115.8 0 0 0

ONLINE
R01 50 101.6 200 0.0 101.6 0 0 0

ONLINE
R02 50 101.6 200 0.0 101.6 0 0 0

ONLINE
R03 50 101.6 200 0.0 201.1 5 0 0

ONLINE Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 30 Jan 2002 07:25:30 -0800

no, you can't shrink a rollback segment below the optimal value

Sinard Xing <sinardyxing_at_bcs-ach.com.sg> wrote:
> Hi,
>
>
> Assume
> INITIAL * MINEXTENT = 2M and also = OPTIMAL
>
> Do you think
> ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M;
>
> will work ?
>
>
>
> Thanks,
>
> Sinardy
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sinard Xing
> INET: sinardyxing_at_bcs-ach.com.sg
>
> 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: 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).

Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

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




MOHAMMAD RAFIQ


_________________________________________________________________
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
   INET: rafiq9857_at_hotmail.com

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: 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.com
--
Author: Deshpande, Kirti
   INET: kirti.deshpande_at_verizon.com

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




MOHAMMAD RAFIQ


_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail. 
http://www.hotmail.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

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

Received on Wed Jan 30 2002 - 13:10:37 CST