// Oracle error reference

ORA-01653: unable to extend table in tablespace

A table needed a new extent and its tablespace had no free space left. Add a data file, resize one, or enable autoextend.

Error code
ORA-01653
Category
Space, memory and limits
Error message
unable to extend table string.string by string in tablespace string

What it means

When a table fills its allocated extents, Oracle allocates another one in the table's tablespace. ORA-01653 means there was no room, for example ORA-01653: unable to extend table APP.EVENTS by 1024 in tablespace USERS. The insert or update that needed the space fails.

Newer releases word it as unable to increase tablespace tablespace_name by storage_allocated storage_units during insert or update on table schema_name.table_name.

Common causes

  • The tablespace's data files are full and can't grow (autoextend off or at MAXSIZE).
  • The disk or ASM disk group holding the data files is full.
  • A big load or a runaway process inserting rows.

How to fix it

  1. Check free space and autoextend settings.

    SELECT file_name, bytes/1024/1024 AS mb, autoextensible, maxbytes/1024/1024 AS max_mb
    FROM dba_data_files WHERE tablespace_name = 'USERS';
  2. Add a data file or let an existing one grow.

    ALTER TABLESPACE users ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
    ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;
  3. Or reclaim space by purging old data and the recycle bin.

    PURGE RECYCLEBIN;

Related errors

More space, memory and limits errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.