Oracle 12c – Unable to Resize System Tablespace

configurationdatabase-sizeoracle

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:

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.

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?

Best Answer

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;

Related Solutions

SQL*Loader-605: Non-data dependent ORACLE error occurred

You've simply run out of space in the DB you are loading the data into. Try:

SQL> select file_name, bytes/1024/1024/1024 as gb from dba_data_files where tablespace_name='USERS';

Let's say the file was `/data/users01.dbf', 32G in size. Then you might do

SQL> alter tablespace users add datafile '/data/users02.dbf' size 1g autoextend on next 1g maxsize unlimited;

That will give you up to another 32G of space. Repeat as necessary, 03, 04 etc. I don't know how much Oracle data your 95G text file will end up as, but you can see what is available with:

SQL> select tablespace_name, sum(bytes)/1024/1024/1024 as Gb from dba_free_space where tablespace_name='USERS' group by tablespace_name;

ORA-01654: unable to extend index by 128 in tablespace

Deleting data will not make any difference as Oracle will have kept the space ready to be re-filled. The top space usage in a table is known as the high water mark. Tom Kyte has a great post about it.

You reduce the high water mark by rebuilding the table:

alter table my_table_name move;

If you're still getting the error after increasing the size of the tablespace there are a couple of possible reasons...

The index is too big even for the newly increased tablespace. How big is the table and what are the combined size of the columns you're indexing?
You're not specifying a tablespace when creating the index and it's not using the one you think, whatever the default tablespace for your schema is.

The answer to 1 to is add more space.

To find out your default tablespace:

select * from dba_users where username = 'MY_USER'

If this is not your index tablespace you have to specify it when creating the index.

create index i_my_table 
    on my_table ( my_column ) 
       tablespace my_index_ts
       < other options >

Yes, if you're in 9i it's definitely worth collecting statistics as it doesn't collect them automatically when enough changes have been made to the table - it does in later versions. Use DBMS_STATS.GATHER_TABLE_STATS() and only do this after rebuilding the table.

Unfortunately in Oracle you can't reduce the size of a tablespace. To use this option you'd have to re-create the tablespace, but smaller, and move everything to it.