Introduction Oracle ORA-01653 `unable to extend table SCHEMA.TABLE by X in tablespace Y` occurs when the tablespace has no contiguous free space large enough to allocate the next extent for the table. This stops all INSERT operations to the affected table until space is freed or the tablespace is expanded.

Symptoms - `ORA-01653: unable to extend table SCHEMA.TABLE by 128 in tablespace USERS` - All INSERT operations to the table fail - `DBA_FREE_SPACE` shows fragmented free space with no large contiguous blocks - Tablespace usage approaching 100% - Alert log showing repeated ORA-01653 errors

Common Causes - Tablespace datafiles at maximum size with no autoextend - Fragmented free space with no contiguous block large enough for the next extent - Large bulk insert or direct-path load consuming all available space - Tablespace not configured with autoextend or with insufficient MAXSIZE - Old data not purged, causing gradual space exhaustion

Step-by-Step Fix 1. **Check tablespace usage and free space": ```sql SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024, 2) AS total_mb, ROUND(SUM(NVL(fs.bytes, 0)) / 1024 / 1024, 2) AS free_mb, ROUND((SUM(df.bytes) - SUM(NVL(fs.bytes, 0))) / SUM(df.bytes) * 100, 1) AS used_pct, COUNT(fs.bytes) AS free_fragments, ROUND(MAX(NVL(fs.bytes, 0)) / 1024 / 1024, 2) AS largest_free_mb FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id WHERE df.tablespace_name = 'USERS' GROUP BY df.tablespace_name; ```

  1. 1.**Add a new datafile to the tablespace":
  2. 2.```sql
  3. 3.ALTER TABLESPACE users
  4. 4.ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf'
  5. 5.SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
  6. 6.`
  7. 7.**Resize existing datafiles":
  8. 8.```sql
  9. 9.-- Find current datafile sizes
  10. 10.SELECT file_name, bytes / 1024 / 1024 AS current_mb,
  11. 11.maxbytes / 1024 / 1024 AS max_mb,
  12. 12.autoextensible
  13. 13.FROM dba_data_files
  14. 14.WHERE tablespace_name = 'USERS';

-- Resize a datafile ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' RESIZE 20G; ```

  1. 1.**If space exists but is fragmented, coalesce the tablespace":
  2. 2.```sql
  3. 3.ALTER TABLESPACE users COALESCE;

-- Check if coalescing helped SELECT tablespace_name, bytes / 1024 / 1024 AS free_mb FROM dba_free_space WHERE tablespace_name = 'USERS' ORDER BY bytes DESC; ```

  1. 1.**Purge old data to reclaim space":
  2. 2.```sql
  3. 3.-- Archive and delete old data
  4. 4.CREATE TABLE orders_archive_2025
  5. 5.AS SELECT * FROM orders WHERE order_date < DATE '2026-01-01';

DELETE FROM orders WHERE order_date < DATE '2026-01-01'; COMMIT;

-- Reclaim space ALTER TABLE orders ENABLE ROW MOVEMENT; ALTER TABLE orders SHRINK SPACE CASCADE; ```

Prevention - Monitor tablespace usage with alerting at 80% and 90% - Configure datafiles with `AUTOEXTEND ON` and appropriate `MAXSIZE` - Implement data retention policies to purge old records - Use tablespace quotas to prevent a single schema from consuming all space - Regularly check `DBA_FREE_SPACE` for fragmentation - Use partitioning to manage large tables with efficient data purging - Plan capacity based on monthly data growth rates