5

Problem:

My java application is returning ORA-01691: unable to extend lob segment errors for every insert

Cause:

After running:

SELECT * 
FROM dba_tablespace_usage_metrics 
ORDER BY used_percent desc;

I found that the SYSTEM datafile is 99.81% full (and is only 4MB for some reason).

Solution Attempts:

  1. I turned autoextend on with

    ALTER DATABASE 
    DATAFILE '/home/user/oracle/orcl/system01.dbf' 
    AUTOEXTEND ON NEXT 2048M 
    MAXSIZE UNLIMITED;
    

    ...which executed fine, but did not increase the size.

  2. I then tried to manually increase the size with:

    ALTER DATABASE 
    DATAFILE '/home/user/oracle/orcl/system01.dbf' 
    RESIZE 2048M;
    

...which gave an error that the new size I was trying to use would end up truncating data. That makes no sense of course since the current size is 4MB. I also tried pasting in 2GB as bytes instead of "2048M" because I thought it may not understand "2048M". That gave the same error though.

What is the actual, working way to get rid of the ORA-01691: unable to extend lob segment error?

5

DBA_TABLESPACE_USAGE_METRICS shows units in database blocks, not bytes. 4 MB is not enough for a SYSTEM tablespace. Most likely what you saw was 4M blocks, which is 32 GB with a 8K blocksize tablespace, and that is the maximum size of a datafile in a 8K smallfile tablespace. That is why your attempts had no effect.

You can add a new datafile to the tablespace, for example:

alter tablespace system add datafile '/home/user/oracle/orcl/system02.dbf' size 10m autoextend on next 10M maxsize unlimited;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.