Oracle Error ORA-01653

Error code

ORA-01653

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

Space, Memory & Resource Limits

Last reviewed

24 October 2026

What it means

ORA-01653 means a table needed another extent to store new or updated rows, and its tablespace had no free space and could not grow, so the INSERT or UPDATE failed.

A table grows in extents allocated from its tablespace. When the free space in the tablespace's datafiles runs out and none of them can autoextend (autoextend is off, a file has reached its MAXSIZE, or the disk is full), Oracle cannot allocate the next extent and raises ORA-01653. The statement fails; other tables in the same tablespace will soon fail too.

The current message names the tablespace, the size of the allocation that failed and the table as schema.table; 19c and 21c print the table first and the size as a number of blocks. Oracle's reference lists the remedies: resize a datafile, add a datafile, enable AUTOEXTEND, raise MAXSIZE (and add storage if MAXSIZE is already unlimited), or resize a bigfile tablespace. Indexes, partitions and temporary segments that cannot grow raise sibling errors (ORA-01654 for an index, ORA-01652 for temporary segments); a user who has hit a tablespace quota gets ORA-01536 instead.

Common causes

• The tablespace's datafiles are full and autoextend is off.
• Autoextend is on but every datafile has reached its MAXSIZE, or a smallfile datafile has reached its maximum number of blocks (platform dependent, typically 4194303 blocks according to the Database Reference).
• The file system or ASM disk group under the datafiles is full, so autoextend cannot succeed.
• An unexpected bulk load, a runaway job inserting in a loop, or missing purging of old data grew a table far beyond its normal size.
• Free space exists but is fragmented into pieces smaller than the extent the table needs (more likely in tablespaces with large or manually set extent sizes).

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT m.tablespace_name,
       ROUND(m.used_space * t.block_size / 1048576)      AS used_mb,
       ROUND(m.tablespace_size * t.block_size / 1048576) AS max_mb,
       ROUND(m.used_percent, 1)                          AS used_pct
FROM dba_tablespace_usage_metrics m
JOIN dba_tablespaces t ON t.tablespace_name = m.tablespace_name
ORDER BY m.used_percent DESC;

SELECT file_name,
       ROUND(bytes / 1048576)    AS size_mb,
       autoextensible,
       ROUND(maxbytes / 1048576) AS max_mb,
       increment_by
FROM dba_data_files
WHERE tablespace_name = 'USERS'
ORDER BY file_name;

SELECT owner, segment_name, segment_type,
       ROUND(bytes / 1048576) AS size_mb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- largest free pieces left
SELECT ROUND(MAX(bytes) / 1048576) AS largest_free_mb,
       ROUND(SUM(bytes) / 1048576) AS total_free_mb
FROM dba_free_space
WHERE tablespace_name = 'USERS';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf' RESIZE 20G;

-- or let it grow on demand, with a ceiling
ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf'
  AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

ALTER TABLESPACE users
  ADD DATAFILE '/u02/oradata/ORCL/users02.dbf' SIZE 10G
  AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

SELECT TRUNC(created_at) AS day, COUNT(*) AS rows_added
FROM shop.orders
WHERE created_at >= SYSDATE - 14
GROUP BY TRUNC(created_at)
ORDER BY day;

DELETE FROM shop.audit_log
WHERE logged_at < ADD_MONTHS(SYSDATE, -24);

Examples

A bulk load into a tablespace that cannot grow

Not reproduced here: no Oracle Database is installed for this site. The result follows the message template in Oracle's current error reference with this example's names filled in; the allocation size is illustrative. Oracle Database 19c and 21c print "ORA-01653: unable to extend table SHOP.ORDERS by N in tablespace USERS", where N is a number of blocks.

-- USERS has one 10 GB datafile with AUTOEXTEND off, and it is full
INSERT INTO orders (order_id, customer_id, order_date, amount)
SELECT order_id, customer_id, order_date, amount
FROM orders_staging;

ORA-01653: unable to increase tablespace USERS by 8MB during insert or update on table SHOP.ORDERS

Autoextend reached its MAXSIZE

Not reproduced here: no Oracle Database is installed for this site. This shows the 19c and 21c message format from Oracle's error reference; the block count is illustrative. Even a single-row insert fails when the table needs a new extent; current releases print the "unable to increase tablespace ..." form.

-- users01.dbf is AUTOEXTEND ON ... MAXSIZE 30G and is now 30 GB
INSERT INTO audit_log (logged_at, user_name, action)
VALUES (SYSTIMESTAMP, 'SHOP_APP', 'LOGIN');

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

How to prevent it

Monitor DBA_TABLESPACE_USAGE_METRICS and alert well before a tablespace is full, and keep autoextend enabled with a MAXSIZE that the file system can actually provide. Track the growth rate of the largest tables so you can add space before it runs out.

Give large or fast-growing tables (logs, audit, staging) their own tablespace and a retention job, so one runaway table cannot stop inserts into everything else.

Common questions

Official documentation

• ORA-01653 (Oracle Database Error Messages)
• DBA_DATA_FILES (Oracle Database Reference 19c)
• DBA_TABLESPACE_USAGE_METRICS (Oracle Database Reference 19c)
• ALTER TABLESPACE (Oracle SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.