Diagnosing ORA-01653: When AUTOEXTEND Is Enabled but the Tablespace Still Cannot Grow

A common Oracle production error is:

ORA-01653: unable to extend table ... by 128 in tablespace ...

At first glance, the solution may seem obvious: enable AUTOEXTEND on the datafile. An important lesson from a production issue I encountered, however, is that AUTOEXTEND does not mean unlimited growth.

The Problem

An application began receiving errors because Oracle could no longer extend a table in its tablespace. I first checked the datafiles:

SELECT file_name,
       bytes / 1024 / 1024 AS size_mb,
       autoextensible,
       maxbytes / 1024 / 1024 AS max_mb
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA';

The result looked approximately like this:

FILE_NAME                                  SIZE_MB   AUTOEXTENSIBLE   MAX_MB
----------------------------------------  --------  ---------------  --------
.../app_data01.dbf                         32767.7   YES              32768

This immediately explained the problem. The file was configured for AUTOEXTEND, but it had already grown to almost its maximum size.

Why AUTOEXTEND Did Not Help

For a traditional Oracle smallfile tablespace with an 8 KB block size, an individual datafile is commonly limited to about 32 GB. So although AUTOEXTENSIBLE = YES, Oracle had essentially nowhere left to extend the file.

The important columns are therefore not just AUTOEXTENSIBLE, but also BYTES and MAXBYTES.

The Solution

Instead of trying to make the existing file larger, the appropriate solution was to provide the tablespace with additional capacity, for example by adding another datafile:

ALTER TABLESPACE app_data
ADD DATAFILE '/u01/app/oracle/oradata/orcl/app_data02.dbf'
SIZE 5G
AUTOEXTEND ON
NEXT 512M
MAXSIZE 32G;

The exact file size, growth increment, and maximum size should be selected according to the environment and expected growth.

A Better Troubleshooting Check

When investigating ORA-01653, I check the current and maximum datafile sizes together:

SELECT tablespace_name,
       file_name,
       bytes / 1024 / 1024 AS size_mb,
       autoextensible,
       maxbytes / 1024 / 1024 AS max_mb
FROM dba_data_files;