What's Actually Happening

Oracle database reports tablespace full error. Cannot allocate more space for data.

The Error You'll See

sql
ORA-01653: unable to extend table MYUSER.MYTABLE by 1024 in tablespace USERS

Why This Happens

  1. 1.Datafile full - Datafile reached max size
  2. 2.Autoextend off - Autoextend not enabled
  3. 3.Disk full - No disk space for autoextend
  4. 4.Fragmentation - No contiguous space

Step 1: Check Tablespace Usage

sql
SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024,2) AS size_mb,
       ROUND(MAX(bytes)/1024/1024,2) AS max_size_mb,
       ROUND(SUM(bytes)/1024/1024,2) - ROUND(MAX(bytes)/1024/1024,2) AS free_mb
FROM dba_data_files
WHERE tablespace_name = 'USERS'
GROUP BY tablespace_name;

Step 2: Add Datafile

sql
ALTER TABLESPACE users ADD DATAFILE 
  '/u01/app/oracle/oradata/mydb/users02.dbf' 
  SIZE 1G 
  AUTOEXTEND ON 
  NEXT 100M 
  MAXSIZE 10G;

Step 3: Resize Existing Datafile

sql
ALTER DATABASE DATAFILE 
  '/u01/app/oracle/oradata/mydb/users01.dbf' 
  RESIZE 10G;

Step 4: Enable Autoextend

sql
ALTER DATABASE DATAFILE 
  '/u01/app/oracle/oradata/mydb/users01.dbf' 
  AUTOEXTEND ON 
  NEXT 100M 
  MAXSIZE 10G;

Oracle Tablespace Checklist

CheckQueryExpected
Usagedba_data_files< max
Autoextenddba_data_filesON
Disk spacedf -hAvailable

Verify the Fix

sql
SELECT tablespace_name, bytes/1024/1024 AS mb FROM dba_data_files;
-- Output: Extended size
  • [Fix Oracle Connection Failed](/articles/fix-oracle-connection-failed)
  • [Fix Oracle Performance Slow](/articles/fix-oracle-performance-slow)