Re: Mystery "unable to extend" error

[Janine Sisk <janine@xxxxxxxxxx>]

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: Bradd Piontek
  • RE: Mystery "unable to extend" error
    • From: Mercadante, Thomas F (LABOR)

Other related posts:

Mystery "unable to extend" error

RE: Mystery "unable to extend" error

Re: Mystery "unable to extend" error

Re: Mystery "unable to extend" error

RE: Mystery "unable to extend" error

Re: Mystery "unable to extend" error