SQLlightspeed logo SQLlightspeed
Oracle errors

ORA-01653: unable to extend table

ORA-01653: unable to extend table SCHEMA.TABLE by N in tablespace USERS

The table needs a new extent and the tablespace has no room for it. Check the free space and the autoextend limits, then add or resize a datafile.

What ORA-01653 means

Tables grow in extents. When a new extent does not fit, the insert or update that needed it fails with ORA-01653. This can happen with free space still reported in the tablespace if it is split into pieces too small for the extent, or if the datafiles have reached their MAXSIZE. For indexes the same error is ORA-01654.

Common causes of ORA-01653

How to fix ORA-01653

  1. Check used and free space, and each datafile’s autoextend setting: DBA_DATA_FILES and DBA_FREE_SPACE.
  2. Add a datafile, or resize an existing one.
  3. Turn on autoextend with a sensible limit, and confirm the disk has the room.
  4. Check which tables grew and whether old data should be archived or purged.

ORA-01653 example

Datafiles and their limits:

SELECT file_name, ROUND(bytes / 1024 / 1024) AS mb, autoextensible,
       ROUND(maxbytes / 1024 / 1024) AS max_mb
  FROM dba_data_files
 WHERE tablespace_name = 'USERS';

Add room:

ALTER TABLESPACE users ADD DATAFILE SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 30G;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Related errors

ORA-01652ORA-00257ORA-01031 All Oracle errors →

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-01653. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.