The ORA-01653 error occurs when Oracle cannot allocate additional space for a segment (table, index, etc.) inside a tablespace.
Typical error message:
ORA-01653: unable to extend table <schema.table_name> by <n> in tablespace <tablespace_name>
This means Oracle tried to allocate a new extent for the object but the tablespace did not have enough free space.
1. What ORA-01653 Means
Oracle stores data in the following hierarchy:
Tablespace
→ Datafile
→ Segment (table/index)
→ Extent
→ Block
When a table grows, Oracle allocates extents.
If:
No free space exists in the tablespace
Datafiles cannot grow
Autoextend is disabled
Oracle raises:
ORA-01653: unable to extend segment
Example:
ORA-01653: unable to extend table HR.EMPLOYEES by 128 in tablespace USERS
Meaning:
Oracle tried to allocate 128 blocks but failed.
2. Why Tablespace Becomes Full
Several reasons can cause this error.
1. Datafiles reached maximum size
A tablespace consists of datafiles.
If a datafile reaches its maxsize, Oracle cannot extend it further.
Example:
users01.dbf = 10GB maxsize
Once full → ORA-01653 occurs.
2. Autoextend is OFF
If autoextend is disabled, Oracle cannot automatically increase datafile size.
Check example:
AUTOEXTEND OFF
Then the file stops growing.
3. Tables growing very fast
Common causes:
Large inserts
Batch jobs
ETL loads
Logging tables
Archival tables
Example:
INSERT INTO big_table SELECT * FROM source_table;
This may fill tablespace quickly.
4. Large index creation
Indexes require large temporary space.
Example:
CREATE INDEX idx1 ON huge_table(column1);
Index creation may trigger ORA-01653.
5. Fragmented free space
Sometimes tablespace has free space but not contiguous extents.
Oracle cannot allocate the required extent.
6. Temp tablespace full
If the error occurs during sorting:
ORA-01652: unable to extend temp segment
This is similar but occurs in TEMP tablespace.
3. Queries to Check Tablespace Usage
Before fixing, we must analyze usage.
Query 1 — Tablespace Size and Free Space
SELECT
df.tablespace_name,
ROUND(df.total_mb) total_mb,
ROUND(fs.free_mb) free_mb,
ROUND((df.total_mb - fs.free_mb)) used_mb,
ROUND((fs.free_mb / df.total_mb) * 100,2) free_percent
FROM
(SELECT tablespace_name,
SUM(bytes)/1024/1024 total_mb
FROM dba_data_files
GROUP BY tablespace_name) df
JOIN
(SELECT tablespace_name,
SUM(bytes)/1024/1024 free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY free_percent;
Output example:
| TABLESPACE | TOTAL_MB | FREE_MB | USED_MB | FREE% |
|---|---|---|---|---|
| USERS | 10240 | 100 | 10140 | 0.9 |
This indicates the tablespace is nearly full.
Query 2 — Datafile Size
SELECT
file_name,
tablespace_name,
bytes/1024/1024 MB,
autoextensible,
maxbytes/1024/1024 MAX_MB
FROM dba_data_files;
Shows:
file size
max size
autoextend status
Query 3 — Free Space per Tablespace
SELECT
tablespace_name,
SUM(bytes)/1024/1024 FREE_MB
FROM dba_free_space
GROUP BY tablespace_name;
Query 4 — Largest Segments
Useful to identify which object is consuming space.
SELECT
owner,
segment_name,
segment_type,
tablespace_name,
bytes/1024/1024 MB
FROM dba_segments
ORDER BY bytes DESC;
Query 5 — Datafile Usage %
SELECT
file_name,
bytes/1024/1024 SIZE_MB,
maxbytes/1024/1024 MAX_MB,
(autoextensible)
FROM dba_data_files;
4. Solutions to Fix ORA-01653
There are multiple fixes depending on the situation.
Solution 1 — Add New Datafile (Best Solution)
Add another datafile to the tablespace.
ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/users02.dbf'
SIZE 5G;
Example:
ALTER TABLESPACE USERS
ADD DATAFILE '/data/oracle/users02.dbf'
SIZE 10G AUTOEXTEND ON;
Advantages:
Safest solution
No downtime
Immediate fix
Solution 2 — Resize Existing Datafile
If disk space is available, increase file size.
ALTER DATABASE DATAFILE
'/u01/oradata/users01.dbf'
RESIZE 8G;
Example:
ALTER DATABASE DATAFILE
'/data/oracle/users01.dbf'
RESIZE 20G;
Note:
Resize must be larger than current used space.
Solution 3 — Enable Autoextend
If autoextend is disabled, enable it.
ALTER DATABASE DATAFILE
'/u01/oradata/users01.dbf'
AUTOEXTEND ON;
Better version:
ALTER DATABASE DATAFILE
'/u01/oradata/users01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;
Meaning:
Grow by 100MB each time
Maximum 20GB
Solution 4 — Add Multiple Datafiles
Large production systems often use multiple files.
ALTER TABLESPACE users
ADD DATAFILE '/data/users03.dbf' SIZE 5G AUTOEXTEND ON;
Solution 5 — Clean Unused Objects
Sometimes old objects consume space.
Find large objects:
SELECT
owner,
segment_name,
bytes/1024/1024 MB
FROM dba_segments
ORDER BY bytes DESC;
Then:
DROP TABLE old_table;
Or:
TRUNCATE TABLE old_table;
Solution 6 — Rebuild Index
Indexes may occupy large space.
ALTER INDEX index_name REBUILD;
Or move to different tablespace.
ALTER INDEX index_name
REBUILD TABLESPACE index_ts;
Solution 7 — Shrink Segments
If ASSM enabled:
ALTER TABLE table_name ENABLE ROW MOVEMENT;
ALTER TABLE table_name SHRINK SPACE;
Example Real Scenario
Error:
ORA-01653: unable to extend table CDR.CALL_RECORDS
Diagnosis:
SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb
FROM dba_free_space
GROUP BY tablespace_name;
Result:
CDR_TS free space = 10 MB
Fix:
ALTER TABLESPACE CDR_TS
ADD DATAFILE '/data/cdr02.dbf'
SIZE 10G AUTOEXTEND ON;
Problem solved.
Best Practices to Prevent ORA-01653
1. Enable Autoextend
AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
2. Monitor tablespace usage
Use monitoring query:
SELECT
tablespace_name,
ROUND(used_percent,2)
FROM dba_tablespace_usage_metrics;
3. Create alert threshold
Example:
Alert if usage > 85%
4. Separate tablespace types
Good design:
DATA_TS
INDEX_TS
TEMP_TS
UNDO_TS
5. Plan capacity
Monitor growth trend.
Quick ORA-01653 Troubleshooting Checklist
Step 1
Check tablespace usage
dba_tablespace_usage_metrics
Step 2
Check datafiles
dba_data_files
Step 3
Check free space
dba_free_space
Step 4
Add datafile / resize / enable autoextend
