Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

Re: Mystery "unable to extend" error

From: "Bradd Piontek" <piontekdd@xxxxxxxxx>
To: janine@xxxxxxxxxx
Date: Mon, 2 Jun 2008 19:02:19 -0500

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@xxxxxxxxxx> 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
>
>

References:

Re: Mystery "unable to extend" error
From: Janine Sisk

Re: Mystery "unable to extend" error

Other related posts: