<\!DOCTYPE html>
🗄️ Storage Emergency

# Oracle Tablespace Full Recovery Runbook

⏱ 10–30 min recovery Oracle 12c / 19c / EBS 12.2 Updated 2026-05-15
🔴 Production emergency?
If ORA-01653 or ORA-01688 is actively blocking transactions, jump directly to Step 3: Emergency Space Relief. Run diagnostics after you've bought breathing room.

## 1. Recognising the Symptoms

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

ORA-01653
`ORA-01653: unable to extend table OWNER.TABLE_NAME by 128 in tablespace TBS_NAME`
Standard data tablespace is full. The number after "by" is the number of Oracle blocks the segment needed.
ORA-01688
`ORA-01688: unable to extend table OWNER.TABLE_NAME partition PART by 128 in tablespace TBS_NAME`
Same error for partitioned tables. Same fix.

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;

⚠️ MAXSIZE UNLIMITED warning
Setting MAXSIZE UNLIMITED means Oracle can grow the datafile until the filesystem/diskgroup is full. Use a specific max if you need to protect other components sharing the same filesystem.

### 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

🔴 SYSTEM tablespace full = database is down
Oracle cannot allocate space for internal operations. This is a P1 incident. Add a datafile immediately.

    -- 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.

✅ Prevention checklist

- AUTOEXTEND ON with a realistic MAXSIZE on all datafiles
- Alert on tablespace > 80% (warning) and > 90% (critical)
- Automated weekly space trend reports
- Purge policy on audit trail, log, and staging tables
- AWR retention review: default 8 days of AWR in SYSAUX adds up

### Catch this before it pages you

TuneVault monitors all tablespace utilisation continuously, alerts at 80%/90% thresholds, and shows the exact datafiles and top segments in your health check report.

Run a Free Health Check →