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: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
To: <janine@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 3 Jun 2008 07:40:03 -0400

Janine,
 
Did you try "alter table modify lob(lob_column) (storage (next 1M
pctincrease 0))"?   This is supposed to change the LOB storage params.
 
If that doesn't work, you can move the lob segments to another
tablespace and fix the storage parameters there.
 
By the way, this is why I hate the pctincrease.  It's always been a
problem since they introduced it.  To me, it always causes more problems
than what it is supposed to help.  It would make sense if they also
provided an uppermost limit to the parameter (like pctincrease until it
gets to x size).  But I digress.  This is solved with other newer
parameters.
 
Hope this helps.

Tom
 
 
________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Janine Sisk
Sent: Monday, June 02, 2008 7:48 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Mystery "unable to extend" error


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

On May 30, 2008, at 1:51 PM, Janine Sisk wrote:


        This is an old 8.1.7 installation on Linux, which suffers from
the "2 GB per file" problem.  A file upload started giving "ORA-01691:
unable to extend lob segment" errors so I added another data file to the
tablespace, but inexplicably (to me) the errors continue. 

        There is now plenty of free space in the tablespace:

        Tablespace Name         Total Bytes            Used
Free   %Used     
        ---------------- ------------------ ---------------
---------------- ------     
        USER_LOB          17,112,760,320  14,613,618,688
2,499,141,632   85.4     

        And yet..

        error in `OCILobWrite ()': ORA-01691: unable to extend lob
segment USER.SYS_LOB0000041854C00017$$ by 599124 in tablespace USER_LOB
        
        
        (where I have replaced the actual user name with USER)

        I have never seen this before;  adding another data file has
always been the solution in the past.  I don't even know where to look
next.  Any suggestions?

        thanks,

        janine

        
        ---
        Janine Sisk
        President/CEO of furfly, LLC
        503-693-6407






---
Janine Sisk
President/CEO of furfly, LLC
503-693-6407

Follow-Ups:

Re: Mystery "unable to extend" error
From: Niall Litchfield

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

RE: Mystery "unable to extend" error

Other related posts: