very strange.... got the same results you did. But when I did my own
testing I didn't shrink below initial*min_extents
Now, I had done one thing you didn't, which was to run a delete that
would force the rollback segment to extend before I tried the shrink.
Strangeness abounds
Anyone know someone in Oracle who can go in and read the code? <G>
- Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> doesn't sound like you are doing anything stupid, and I ran on
> 8.1.6.0
> and it said it would shrink it but didn't. I'll use your test case
> against my databases in the morning
>
>
> --- "Johnston, Tim" <TJohnston_at_quallaby.com> wrote:
> > Ok... I've ran this test case on both a 8.1.6.0 and a 8.1.7.3
> > database...
> > I've ran it with both public and private rollback segments (just in
> > case)...
> > I've looked at rssize in v$rollstat and sum(bytes) from
> > dba_segments...
> > And, in all cases, it allows me to shrink the segment to less then
> > both
> > optimal and min_extents*extent_size even though
> > optimal=min_extents*extent_size... Is there something wrong in the
> > test
> > case? Has anyone ran this on one of their test databases? I'm
> > confused
> > since the test case seems straight forward... The way I read, it
> > allows me
> > to perform the shrink and the resulting segment is actually the
> size
> > I
> > specify... Am I doing something stupid in the test case?
> >
> > Thanks
> > Tim
> >
> > PS - Here is the test case again...
> >
> > 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:31 PM
> > 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.
> > --
> > 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).
>
=== message truncated ===
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).
Received on Thu Jan 31 2002 - 07:23:47 CST
