RE: ALTER ROLLBACK SEGMENT rbs SHRINK

From: Sinard Xing <sinardyxing_at_bcs-ach.com.sg>
Date: Thu, 31 Jan 2002 22:03:51 -0800
Message-ID: <F001.00402E13.20020131214519@fatcity.com>

Oracle said, optimal value will automatically shrink your rbs (with continuous extents off course).
Since this is the case do you think is good to add the value of optimal.

Sinardy

-----Original Message-----
Tim
Sent: 01 February 2002 12:30
To: Multiple recipients of list ORACLE-L

Can what cause a ORA-1555? A shrink? If that is the question... The answer is yes...

Tim

-----Original Message-----
Sent: Thursday, January 31, 2002 11:00 PM To: Multiple recipients of list ORACLE-L

Will this cause ora 1555 ?

-----Original Message-----
Tim
Sent: 31 January 2002 03:31
To: Multiple recipients of list ORACLE-L

Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine...

SQL> create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M );

Rollback segment created.

SQL> alter rollback segment tim online;

Rollback segment altered.

SQL> select segment_name, min_extents, initial_extent, next_extent 2 from dba_segments
3 where segment_name = 'TIM';

SEGMENT_NAME                   MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT
------------------------------ ----------- -------------- -----------
TIM                                     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 = 'TIM'
  5 and drs.segment_id = rs.usn;

EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576

---------- ----------------- ------------------
        20         20.296875                 20

SQL> alter rollback segment TIM shrink to 10M;

Rollback segment altered.

SQL> select segment_name, min_extents, initial_extent, next_extent 2 from dba_segments
3 where segment_name = 'TIM';

SEGMENT_NAME                   MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT
------------------------------ ----------- -------------- -----------
TIM                                     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 = 'TIM'
  5 and drs.segment_id = rs.usn;

EXTENTS RSSIZE OPTSIZE
---------- ---------- ----------

10 10633216 20971520

SQL> alter rollback segment tim offline;

Rollback segment altered.

SQL> drop rollback segment tim;

Rollback segment dropped.

SQL> What version is your test running on?

Tim

PS - Here is my test case if you want to run it...

column segment_name format a30
create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online;
select segment_name, min_extents, initial_extent, next_extent from dba_segments
where segment_name = 'TIM';
select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs,

       v$rollstat rs
where drs.segment_name = 'TIM'
   and drs.segment_id = rs.usn;
alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent   from dba_segments
where segment_name = 'TIM';
select rs.extents, rs.rssize, rs.optsize   from dba_rollback_segs drs,

v$rollstat rs
where drs.segment_name = 'TIM'
and drs.segment_id = rs.usn;
alter rollback segment tim offline;
drop rollback segment tim;

-----Original Message-----
Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L

testing....

if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal.

if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents.

so you can "kinda" shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it.

"Johnston, Tim" <TJohnston_at_quallaby.com> wrote:
> You know what? I thought the same thing... But, the curious side of
> me
> decided to give it a try to be sure... And guess what... It works?
> Learn
> something new everyday...
>
> SQL> select segment_name,sum(bytes) from dba_segments where
> tablespace_name
> = 'RBS' and segment_name
> = 'RBS17' group by segment_name;
>
> SEGMENT_NAME SUM(BYTES)
> ------------------------ ----------
> RBS17 22364160
>
>
> SQL> select rs.optsize, rs.extents
> 2 from dba_rollback_segs drs,
> 3 v$rollstat rs
> 4 where drs.segment_name = 'RBS17'
> 5 and drs.segment_id = rs.usn;
>
> OPTSIZE EXTENTS
> ---------- ----------
> 22020096 21
>
> SQL> alter rollback segment RBS17 shrink to 10M;
>
> Rollback segment altered.
>
> SQL> select segment_name,sum(bytes) from dba_segments where
> tablespace_name
> = 'RBS' and segment_name
> = 'RBS17' group by segment_name;
>
> SEGMENT_NAME SUM(BYTES)
> ------------------------- ----------
> RBS17 10649600
>
>
> SQL> select rs.optsize, rs.extents
> 2 from dba_rollback_segs drs,
> 3 v$rollstat rs
> 4 where drs.segment_name = 'RBS17'
> 5 and drs.segment_id = rs.usn;
>
> OPTSIZE EXTENTS
> ---------- ----------
> 22020096 10
>
> SQL>
>
> 8.1.7.3 on Solaris 2.8
>
> Tim
>
> -----Original Message-----
> Sent: Wednesday, January 30, 2002 10:26 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).
> --
> 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).

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

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

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

Received on Fri Feb 01 2002 - 00:03:51 CST