Oracle Database 12c: Unable to resize system tablespace

Question

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?

Answer 1

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;