ORA-01653: Unable to Extend Table or Index – How to Fix Oracle Tablespace Space Errors

When a critical application process suddenly fails and the alert log screams this message:

ORA-01653: unable to extend table <TABLE_NAME> by 128 in tablespace <TABLESPACE_NAME>

It means one thing — your tablespace has run out of space.
This is one of the most common storage issues in Oracle Database, and if left unaddressed, it can halt inserts, updates, and even system jobs.

Let’s go through why it happens, how to fix it immediately, and how to prevent it permanently.

---

What Does ORA-01653 Mean?

The ORA-01653: unable to extend table or index error indicates that Oracle tried to allocate an additional extent (a chunk of space) to a table, index, or segment — but the tablespace has no free contiguous space left to accommodate it.

Simply put:

The database wanted to grow, but the tablespace couldn’t.

---

Common Causes of ORA-01653

1. Tablespace is full – The datafile has no free space left.
2. Autoextend is disabled – The datafile cannot grow automatically.
3. Maximum datafile size reached – Usually in smallfile tablespaces.
4. Temporary tablespace full – Large sorts or operations fill up temp space.
5. Segment size exceeds available contiguous free space.

---

Step-by-Step Troubleshooting Guide

Let’s fix it properly — without risking data corruption or downtime.

---

Step 1: Identify Which Tablespace Is Full

Run:

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

This shows the size, max size, and whether autoextend is enabled.

---

Step 2: Check Free Space in the Tablespace

SELECT tablespace_name, 
       SUM(bytes)/1024/1024 AS free_mb 
FROM dba_free_space 
GROUP BY tablespace_name 
ORDER BY free_mb;

If the free space is very low (under 100 MB for large systems), you’re running out of room.

---

Step 3: Verify Which Object Caused the Error

The alert log will show something like:

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

You can confirm using:

SELECT segment_name, segment_type, tablespace_name, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name='USERS'
ORDER BY bytes DESC;

This helps identify which object or segment is consuming most of the space.

---

Step 4: Quick Fix – Add or Resize Datafile

If your tablespace is full, the fastest solution is to add a new datafile or resize the existing one.

Option 1: Resize existing datafile

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 4G;

Option 2: Add a new datafile

ALTER DATABASE ADD DATAFILE '/u02/oradata/ORCL/users02.dbf' SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

💡 Always ensure there’s enough OS-level disk space before resizing.

---

Step 5: Enable Autoextend (Recommended)

Prevent the same issue in the future:

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

This ensures Oracle automatically expands the datafile as needed — within limits.

---

Step 6: Check and Clean Unused Segments (Optional)

You can reclaim space by removing old data or rebuilding indexes.

To find largest segments:

SELECT owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 AS size_mb
FROM dba_segments
ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

To shrink or move objects:

ALTER TABLE HR.OLD_LOGS MOVE TABLESPACE USERS;
ALTER INDEX HR.IDX_LOGS REBUILD TABLESPACE USERS;

---

Step 7: Check Temporary Tablespace (if error in TEMP)

Sometimes, ORA-01653 happens in temporary tablespaces used for sorting.

SELECT tablespace_name, SUM(bytes_used)/1024/1024 AS used_mb, 
       SUM(bytes_free)/1024/1024 AS free_mb 
FROM v$temp_space_header GROUP BY tablespace_name;

If TEMP is full:

ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 4G;

or

ALTER DATABASE ADD TEMPFILE '/u02/oradata/ORCL/temp02.dbf' SIZE 2G AUTOEXTEND ON;

---

Real Example from the Field

A logistics client’s reporting job failed every night with:

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

Upon checking:

SELECT tablespace_name, free_mb FROM dba_free_space WHERE tablespace_name='DATA';

showed only 5 MB free.
We added a new 5 GB datafile with autoextend enabled:

ALTER DATABASE ADD DATAFILE '/u02/oradata/DATA02.dbf' SIZE 5G AUTOEXTEND ON NEXT 200M;

The job completed successfully, and we added proactive monitoring to prevent recurrence.

---

Best Practices to Prevent ORA-01653

1. Enable AUTOEXTEND for all production datafiles.
2. Monitor tablespace growth with custom scripts or OEM alerts.
3. Regularly analyze large objects with DBA_SEGMENTS.
4. Automate email alerts when free space < 10%.
5. Purge old partitions, logs, or archive data regularly.
6. Keep OS disk usage under 80% to allow database growth.

---

Related Articles

• ORA-01092 and ORA-39701 While Opening Database in Upgrade Mode – Fix Guide
• ORA-01555: Snapshot Too Old – Deep Dive into Undo and Query Consistency
• ORA-01114 / ORA-01110: Cannot Add Datafile or Write to File – Causes, Fixes & Prevention (Real Oracle DBA Guide)

---

Final Thoughts

The ORA-01653: Unable to Extend Table or Index error isn’t dangerous — it’s Oracle’s way of telling you it needs more space to grow.

By monitoring tablespaces regularly, enabling autoextend, and maintaining disk capacity, you can ensure your database keeps running smoothly — no unexpected surprises during production hours.

Proactive space management is one of the simplest — yet most powerful — habits of a professional DBA.