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
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
-
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';
-
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;
-
Or reclaim space by purging old data and the recycle bin.
PURGE RECYCLEBIN;
Related errors
ORA-01652unable to extend temp segmentORA-01950no privileges on tablespace1105SQL Server: Filegroup is full, cannot allocateSQLITE_FULLSQLite: database or disk is full
More space, memory and limits errors
ORA-01555snapshot too oldORA-30036unable to extend segment in undo tablespaceORA-04031unable to allocate bytes of shared memoryORA-01000maximum open cursors exceededORA-00018maximum number of sessions exceededORA-00020maximum number of processes exceededORA-00257archiver error
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.