1. Recognising the Symptoms

Oracle raises one of two errors when a tablespace cannot allocate a new extent:

Other signs a tablespace emergency is developing:

• Alert log entries: Errors in file ... ORA-01653 or Checkpoint not complete
• Application errors: "Cannot insert row", "Transaction failed", "ORA-01652" (temp)
• TuneVault health check shows tablespace > 85% or > 90% used
• Concurrent Manager requests stuck in "Running" with no progress (EBS)

2. Diagnose: Which Tablespace is Full

2a. Check all tablespace utilisation

-- Current tablespace usage (all types)
SELECT
  df.tablespace_name                                    AS tablespace,
  df.total_mb,
  (df.total_mb - NVL(fs.free_mb, 0))                  AS used_mb,
  NVL(fs.free_mb, 0)                                   AS free_mb,
  ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 1) AS pct_used,
  df.autoextend_max_mb
FROM (
  SELECT tablespace_name,
    ROUND(SUM(bytes)/1048576, 1)     AS total_mb,
    ROUND(SUM(DECODE(autoextensible,'YES',maxbytes,bytes))/1048576,1) AS autoextend_max_mb
  FROM dba_data_files GROUP BY tablespace_name
) df
LEFT JOIN (
  SELECT tablespace_name, ROUND(SUM(bytes)/1048576, 1) AS free_mb
  FROM dba_free_space GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC NULLS LAST;

2b. Check temp tablespace separately

-- Temp tablespace (uses dba_temp_files, not dba_data_files)
SELECT
  tablespace_name,
  ROUND(SUM(bytes)/1048576, 1)       AS total_mb,
  ROUND(SUM(bytes - user_bytes)/1048576, 1) AS used_mb,
  ROUND(SUM(user_bytes)/1048576, 1)  AS free_mb,
  ROUND((SUM(bytes - user_bytes) / SUM(bytes)) * 100, 1) AS pct_used
FROM dba_temp_files
GROUP BY tablespace_name;

2c. Find which segments are consuming the space

-- Top 10 space consumers in the problem tablespace
SELECT owner, segment_name, segment_type,
  ROUND(SUM(bytes)/1048576, 1) AS size_mb
FROM dba_segments
WHERE tablespace_name = '&TABLESPACE_NAME'
GROUP BY owner, segment_name, segment_type
ORDER BY size_mb DESC
FETCH FIRST 10 ROWS ONLY;

2d. Check datafile AUTOEXTEND status

-- Which datafiles can autoextend and how much room is left
SELECT
  file_name,
  ROUND(bytes/1048576, 1)    AS current_mb,
  autoextensible,
  ROUND(maxbytes/1048576, 1) AS max_mb,
  ROUND((maxbytes - bytes)/1048576, 1) AS headroom_mb
FROM dba_data_files
WHERE tablespace_name = '&TABLESPACE_NAME'
ORDER BY file_name;

3. Immediate Space Relief

If you need to unblock transactions immediately without waiting for a datafile add, these steps buy time:

3a. Enable AUTOEXTEND on an existing datafile

-- If a datafile exists but AUTOEXTEND is OFF, this is the fastest fix
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
  AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

-- For ASM diskgroups:
ALTER DATABASE DATAFILE '+DATA/ORCL/DATAFILE/users.256.987654321'
  AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

3b. Add a new datafile (preferred for large spaces)

-- Add a 2GB datafile with autoextend to an existing tablespace
ALTER TABLESPACE USERS ADD DATAFILE
  '/u01/app/oracle/oradata/ORCL/users02.dbf'
  SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

-- For Oracle Managed Files (OMF) — Oracle chooses the path:
ALTER TABLESPACE USERS ADD DATAFILE
  SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

4. Permanent Fix: Add Datafiles or Resize

4a. Best practice: size new datafiles correctly

Adding many small datafiles is worse than a few large ones. Oracle limits datafiles per tablespace (default 1023 with SMALLFILE tablespace; BIGFILE tablespaces have no per-datafile limit). Target 3–6 datafiles per tablespace, sized to last 6–12 months.

-- Calculate current monthly growth rate
SELECT
  segment_name,
  ROUND(SUM(bytes)/1048576, 1) AS current_size_mb
FROM dba_segments
WHERE tablespace_name = '&TABLESPACE_NAME'
  AND owner = '&OWNER'
GROUP BY segment_name
ORDER BY current_size_mb DESC;

4b. Resize an existing datafile (if you prefer fewer files)

-- Resize datafile to 10GB (must be larger than current HWM)
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
  RESIZE 10G;

4c. Convert to BIGFILE tablespace for simplicity

BIGFILE tablespaces have a single datafile that can grow up to 128TB (32TB on standard block size). Ideal for large tables. Note: you cannot add additional datafiles to a BIGFILE tablespace.

-- Create a new bigfile tablespace (cannot convert existing)
CREATE BIGFILE TABLESPACE TBS_DATA_BIG
  DATAFILE '/u01/app/oracle/oradata/ORCL/tbs_data_big.dbf'
  SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

5. Special Cases: UNDO, TEMP, SYSTEM

5a. UNDO tablespace full

UNDO tablespace full usually means long-running transactions or an oversized UNDO_RETENTION setting holding space longer than needed.

-- Check active transactions holding undo
SELECT s.sid, s.serial#, s.username, s.status,
  ROUND(t.used_ublks * 8192/1048576, 2) AS undo_mb,
  t.start_time
FROM v$transaction t
JOIN v$session s ON s.taddr = t.addr
ORDER BY undo_mb DESC;

-- Add a datafile to the UNDO tablespace
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE
  '/u01/app/oracle/oradata/ORCL/undotbs02.dbf'
  SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

-- Reduce UNDO_RETENTION if it's holding too much (in seconds)
-- Default is 900 (15 min). Check current value:
SHOW PARAMETER undo_retention;
-- Set to 600 seconds (10 min) if not needed longer:
ALTER SYSTEM SET undo_retention = 600 SCOPE=BOTH;

5b. TEMP tablespace full

TEMP full is usually a large sort, hash join, or parallel query. It recovers automatically when the operation completes, but you can add space immediately:

-- Find sessions using the most temp space
SELECT s.sid, s.serial#, s.username, s.sql_id,
  ROUND(u.blocks * 8192/1048576, 1) AS temp_mb
FROM v$sort_usage u
JOIN v$session s ON s.saddr = u.session_addr
ORDER BY temp_mb DESC;

-- Add a tempfile
ALTER TABLESPACE TEMP ADD TEMPFILE
  '/u01/app/oracle/oradata/ORCL/temp02.dbf'
  SIZE 4G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

-- Shrink temp (reclaim after large sorts complete)
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 2G;

5c. SYSTEM or SYSAUX tablespace full

-- Immediate: add a datafile to SYSTEM
ALTER TABLESPACE SYSTEM ADD DATAFILE
  '/u01/app/oracle/oradata/ORCL/system02.dbf'
  SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

-- SYSAUX: check top consumers (AWR, Statspack, etc.)
SELECT occupant_name, schema_name,
  ROUND(space_usage_kbytes/1024, 1) AS space_mb,
  move_procedure
FROM v$sysaux_occupants
ORDER BY space_usage_kbytes DESC;

6. Verify and Monitor

-- Confirm free space after fix
SELECT
  tablespace_name,
  ROUND(SUM(bytes)/1048576, 1) AS free_mb
FROM dba_free_space
WHERE tablespace_name = '&TABLESPACE_NAME'
GROUP BY tablespace_name;

-- Confirm alert log is clear
-- Look for: no new ORA-01653 entries after your fix
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-01653%'
ORDER BY originating_timestamp DESC;

-- Set up automated alert: create a TuneVault health check
-- to notify when any tablespace exceeds 80%

7. Root Cause and Prevention

Tablespace emergencies are almost always preventable. The root causes in order of frequency:

1. No AUTOEXTEND: Datafiles created without AUTOEXTEND are a disaster waiting to happen. Set AUTOEXTEND ON for all non-SYSTEM datafiles.
2. AUTOEXTEND hits MAXSIZE: MAXSIZE was set too conservatively. Review and increase max on all datafiles.
3. Filesystem / diskgroup full: AUTOEXTEND cannot extend when the OS filesystem is full. Monitor OS-level disk utilisation alongside Oracle tablespace utilisation.
4. Unexpected data growth: A batch job, a runaway audit trail, or a log table growing without bound. Identify top-growing segments and add retention/purge logic.
5. Missing monitoring: A >85% threshold alert catches this before it becomes an emergency.