Re: Mystery "unable to extend" error

This is all without looking at 8i syntax.

1. dba_tablespaces shows the tablespace defaults (i.e. if you create a segment and don't enter one of those parameters, this is the what it'll use). It sounds to me like someone put pctincrease of 50 on the lobsegment.
2. I think this will work "alter table <table_name> modify lob <lob_name> storage(pctincrease 0) or something similar -- Bradd Piontek Twitter: http://www.twitter.com/piontekdd Oracle Blog: http://piontekdd.blogspot.com Linked In: http://www.linkedin.com/in/piontekdd Last.fm: http://www.last.fm/user/piontekdd/

On Mon, Jun 2, 2008 at 6:48 PM, Janine Sisk <janine_at_furfly.net> wrote:

> Ok, I have figured out what the problem is, but even after Googling I don't
> really understand what happened or how to fix it.
> The tablespace was created with pct_increase set to 0:
>
> SQL> select * from dba_tablespaces where tablespace_name = 'USER_LOB';
>
> TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
> ------------------------------ -------------- ----------- -----------
> MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING
> EXTENT_MAN
> ----------- ------------ ---------- --------- --------- ---------
> ----------
> ALLOCATIO PLU
> --------- ---
> USER_LOB 53248 53248 1
> 2147483645 0 0 ONLINE PERMANENT LOGGING
> DICTIONARY
> USER NO
>
>
> However, the segment that's triggering the error has other things in mind:
>
> SQL> select next_extent, pct_increase from dba_segments where segment_name
> = 'SYS_LOB0000041854C00017$$';
>
> NEXT_EXTENT PCT_INCREASE
> ----------- ------------
> 2454011904 50
>
> That's why it's failing; even though I have the space (barely) that's
> larger than an extent can be on Linux with this old version of Oracle.
>
> I'm only an occasional DBA so my knowledge of these things is admittedly
> rusty, but I had thought that objects created within the tablespace would
> inherit their storage specifications from the tablespace. Are LOB segments
> somehow different? Or was my understanding incorrect all along?
>
> Lastly, how do I fix this? I see that I can use an ALTER statement to
> reset the storage parameters, but ALTER what? The table these LOBs are
> associated with lives in a different tablespace. I tried altering the
> table, like so:
>
> alter table user_files storage (next 50K pctincrease 0);
>
> but that didn't affect the attributes of the segment.
>
> Help?
>
> thanks,
>
> janine
>
>

--
http://www.freelists.org/webpage/oracle-l