Re: ALTER TABLE STORAGE doesn't effect next extent

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? Received on Wed May 12 1993 - 05:37:50 CEST