6

I am getting an error:

ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ

But this is very strange to me because the tablespace has AUTOEXTEND on. What is the likely reason that this happens and how can I overcome it?

Thanks.

11

Autoextensible datafiles still have a size limit.

select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';

Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:

1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:

alter database datafile 15 autoextend on maxsize 20G;

Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.

If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:

2) add a new datafile to the tablespace, for example:

alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;
|improve this answer|||||
  • 1
    Me: "maxsize unlimited". Oracle: "so 32GB yeah?". What a pile of garbage Oracle is – Jobbo Feb 3 at 17:07

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.