Sure sure... Make me look again...
:-)
SQL> select segment_name, min_extents, initial_extent, next_extent
2 from dba_segments
3 where segment_name = 'RBS06';
SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT
------------------------------ ----------- -------------- -----------
RBS06 20 1048576 1048576
SQL> select rs.extents, rs.rssize/1048576 , rs.optsize/1048576
2 from dba_rollback_segs drs,
3 v$rollstat rs
4 where drs.segment_name = 'RBS06'
5 and drs.segment_id = rs.usn;
EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576
---------- ----------------- ------------------
21 21.3125 21
SQL> alter rollback segment RBS06 shrink to 10M;
Rollback segment altered.
SQL> select segment_name, min_extents, initial_extent, next_extent
2 from dba_segments
3 where segment_name = 'RBS06';
SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT
------------------------------ ----------- -------------- -----------
RBS06 20 1048576 1048576
SQL> select rs.extents, rs.rssize, rs.optsize
2 from dba_rollback_segs drs,
3 v$rollstat rs
4 where drs.segment_name = 'RBS06'
5 and drs.segment_id = rs.usn;
EXTENTS RSSIZE OPTSIZE
---------- ---------- ----------
10 10633216 22020096
Interesting eh? Doesn't seem like this should work this way but it does...
Again... YMMV... 8.1.7.3 on Solaris...
Tim
-----Original Message-----
Sent: Wednesday, January 30, 2002 1:35 PM
To: Multiple recipients of list ORACLE-L
what are the minimum extents on the rollback segments (specifically
r03) as well as initial and next sizes please?
If optimal is higher than the minimum extents, you're right, it can
shrink down to minimum extents*initial
but it doesn't make a lot of sense to set things up that way
- Mohammad Rafiq <rafiq9857_at_hotmail.com> wrote:
> 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).
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Tim
INET: TJohnston_at_quallaby.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:14:53 CST
