Re: ALTER TABLE STORAGE doesn't effect next extent

In <carl.pedersen-110593232419_at_kip-sn-24.dartmouth.edu> carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) writes:

>In article <CAIGER.93May11124316_at_ress1.afrc.ac.uk>, caiger_at_resa.afrc.ac.uk
>(Andy Caiger -RESCU) wrote:
>>
>>
>> Hello,
>>
>> Can anyone suggest what is going wrong with the following:
>>
>> I have written a bit of PRO*C code that attempts an INSERT into a table, and
>> traps any failure due to errors like:
>>
>> ORA-01547: failed to allocate extent of size X in tablespace 'N'
>>
>> It then uses ALTER TABLE ... STORAGE (NEXT n PCTINCREASE p) to change
>> the storage characteristics of the table, so that repeating
>> the INSERT will result in a smaller extent being allocated.
>>
>> I've checked the data dictionary table 'user_tables' and the ALTER
>> TABLE command definitely changes the right NEXT_EXTENT and
>> PCT_INCREASE values, but the INSERT command still tries to create the
>> same sized extent. Why ? How do you get the ALTER TABLE to have its
>> effect ?
>>
>> I'd be grateful for any suggestions...
>>
>> yours puzzledly,
>> Andy
>>
 

>You didn't say how many extents were in the segment at the time the insert
>failed. I'll bet more than one. You also didn't say what you were using
>for PCTINCREASE. I'll bet not zero.
 

>I'm not 100% sure about this, but I think NEXT only specifies the size of
>the *second* extent allocated, assuming the second extent hasn't been
>allocated yet. The size of the Nth extent is determined by NEXT in
>combination with PCTINCREASE.
 

>It's been a while since I've experimented with this. Can anyone refute or
>support this theory?

It depends on whether we're talking V6 or O7.

V6 behaviour:

    NEXT specifies the size of 2nd extent, and never changes

        size_extent(n) = NEXT * (1 + PCTINCREASE/100)**(n-2)

O7 behaviour:     

    NEXT specifies the size of the next extent to be allocated     ORACLE7 updates NEXT each time a next extent is allocated

        NEXT(n+1) = NEXT(n) * (1 + PCTINCREASE/100)     Setting NEXT specifies the size of next extent to be allocated     (rounded up to multiple of 5 blocks)

Hence if DB-BLOCK_SIZE = 2K

CREATE TABLE foo (...) STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 50);

gives:

		<--- V6 -->	<----------- ORACLE7 ----------->
Extent #	Extent size	Extent size	New value of NEXT
-----------------------------------------------------------------
	1	100K		100K		100K
	2	100K		100K		150K
	3	150K		150K		226K
	4	226K		230K		340K
	5	338K		340K		510K

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.