ORA-25153: Temporary Tablespace Is Empty - Add Temp Files

ORA-25153: Temporary Tablespace Is Empty

Error Overview

Error Text: ORA-25153: Temporary Tablespace is Empty

ORA-25153 occurs when an Oracle session needs temporary space to perform an operation (sort, hash join, index creation, etc.) but the assigned temporary tablespace has no tempfiles — or all of its tempfiles have been dropped, are offline, or failed validation. Without any accessible tempfiles, Oracle cannot allocate any temporary extents and raises this error immediately.

This error is distinct from ORA-01652 (unable to extend temp segment), which means temporary tablespace space is exhausted. ORA-25153 means there is no temp tablespace structure at all, not just that it is full.

The error is most frequently encountered immediately after:

• A database recovery or incomplete recovery (tempfiles are not backed up by RMAN)
• A new database creation where the temporary tablespace definition was omitted
• A DBA accidentally dropped all tempfiles from the temporary tablespace

Common Causes

1. Tempfiles Not Created After Database Recovery

• RMAN does not back up or restore tempfiles by default
• After restore and recovery, the control file references tempfiles that were not restored
• ALTER DATABASE OPEN RESETLOGS succeeded but tempfiles were never recreated

2. Database Cloning or Duplication Without Tempfile Recreation

• RMAN DUPLICATE does not guarantee tempfiles are created on the target
• Manual clone using copy/restore did not include the CREATE TEMPFILE step
• Logical standbys and snapshot standbys may lose temp file references

3. Accidental Tempfile Drop

• DBA dropped all tempfiles from a temporary tablespace during maintenance
• Automation script dropped files based on incorrect file status query
• DROP TABLESPACE with partial re-creation left the tablespace empty

4. Database Created With Temporary Tablespace but No Tempfile

• CREATE TEMPORARY TABLESPACE was issued without an ADD TEMPFILE clause
• This leaves a tablespace definition with no actual storage backing it

5. Tempfile Failed Verification (see also ORA-01187)

• Tempfiles exist in the control file but fail Oracle’s verification checks
• Results in functionally empty temp tablespace from Oracle’s perspective
• Common after restoring a database to a different server

Diagnostic Queries

Check Current Temp Tablespace State

-- All temporary tablespaces and their tempfile count:
SELECT
  ts.tablespace_name,
  ts.status,
  ts.extent_management,
  ts.allocation_type,
  COUNT(tf.file_name)                              AS tempfile_count,
  ROUND(SUM(NVL(tf.bytes, 0)) / 1024 / 1024, 2)   AS total_mb
FROM dba_tablespaces ts
LEFT JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY'
GROUP BY ts.tablespace_name, ts.status, ts.extent_management, ts.allocation_type
ORDER BY ts.tablespace_name;

-- A temporary tablespace with tempfile_count = 0 will cause ORA-25153.

Verify Tempfile Status From the Database Perspective

-- All tempfiles and their status in the control file:
SELECT
  f.file#,
  f.name,
  f.status,
  f.enabled,
  f.bytes / 1024 / 1024 AS size_mb,
  h.status              AS header_status,
  h.error
FROM v$tempfile f
LEFT JOIN v$tempfile_header h ON f.file# = h.file#
ORDER BY f.file#;

-- Tempfiles per tablespace from DBA_ view:
SELECT
  tablespace_name,
  file_name,
  ROUND(bytes / 1024 / 1024, 2) AS size_mb,
  status,
  autoextensible,
  ROUND(maxbytes / 1024 / 1024, 2) AS max_mb
FROM dba_temp_files
ORDER BY tablespace_name;

Check Default Temporary Tablespace Assignments

-- Database-level default temporary tablespace:
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

-- Which users are assigned which temporary tablespace:
SELECT
  username,
  temporary_tablespace,
  account_status
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY temporary_tablespace, username;

-- Users assigned to a temp tablespace that is empty:
SELECT
  u.username,
  u.temporary_tablespace,
  COUNT(tf.file_name) AS tempfile_count
FROM dba_users u
LEFT JOIN dba_temp_files tf ON u.temporary_tablespace = tf.tablespace_name
WHERE u.account_status = 'OPEN'
GROUP BY u.username, u.temporary_tablespace
HAVING COUNT(tf.file_name) = 0
ORDER BY u.temporary_tablespace, u.username;

Measure Current Temporary Space Usage

-- Current temp space usage (useful for sizing the new tempfile):
SELECT
  tablespace_name,
  ROUND(SUM(bytes_used) / 1024 / 1024, 2)  AS used_mb,
  ROUND(SUM(bytes_free) / 1024 / 1024, 2)  AS free_mb,
  ROUND(SUM(bytes_used + bytes_free) / 1024 / 1024, 2) AS total_allocated_mb
FROM v$temp_space_header
GROUP BY tablespace_name;

-- Historical peak temp usage from AWR:
SELECT
  TO_CHAR(sample_time, 'YYYY-MM-DD HH24') AS hour,
  ROUND(MAX(temp_space_allocated) / 1024 / 1024, 2) AS peak_temp_mb
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 30
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24')
ORDER BY hour DESC
FETCH FIRST 24 ROWS ONLY;

Step-by-Step Resolution

1. Confirm the Temp Tablespace Is Empty

-- Identify which temp tablespace has no tempfiles:
SELECT ts.tablespace_name, COUNT(tf.file_name) AS tempfile_count
FROM dba_tablespaces ts
LEFT JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY'
GROUP BY ts.tablespace_name
HAVING COUNT(tf.file_name) = 0;

2. Add a Tempfile to the Empty Tablespace

-- Add a tempfile to fix ORA-25153 immediately:
ALTER TABLESPACE temp
  ADD TEMPFILE '/u01/oradata/orcl/temp01.dbf'
  SIZE 4G AUTOEXTEND ON MAXSIZE 32G;

-- Confirm it is now online:
SELECT file#, name, status FROM v$tempfile_header;

Size guidance:

• Minimum useful size: 512 MB for development databases
• Standard production: 4–16 GB, autoextend enabled
• Size based on historical peak from AWR query above

3. Add Tempfile When Database Is in MOUNT State

After a recovery, the database may not open cleanly. If ORA-25153 is raised during ALTER DATABASE OPEN:

-- You cannot add a tempfile in MOUNT state directly.
-- Option A: Open the database, then add the tempfile:
ALTER DATABASE OPEN;
-- (Database may open with a warning — check alert log)

ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 4G;

-- Option B: If the database has a bad tempfile reference that prevents opening:
ALTER DATABASE TEMPFILE '/path/to/bad_temp.dbf' DROP;
ALTER DATABASE OPEN;
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 4G;

4. Create a Completely New Temporary Tablespace

If the existing TEMP tablespace is damaged or its definition is corrupt:

-- Create a replacement:
CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/oradata/orcl/temp02_01.dbf'
  SIZE 4G AUTOEXTEND ON MAXSIZE 32G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Set it as the new database default:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Reassign any users still pointing to the old empty temp:
-- (DBA can bulk-update users)
SELECT 'ALTER USER ' || username || ' TEMPORARY TABLESPACE temp2;'
FROM dba_users
WHERE temporary_tablespace = 'TEMP'
  AND account_status = 'OPEN';

-- Execute the generated statements, then drop the old tablespace:
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Post-Recovery Tempfile Restoration Script

After every RMAN recovery, run this to ensure temp tablespaces are functional:

-- Check all temp tablespaces for empty state:
SELECT
  ts.tablespace_name,
  COUNT(tf.file_name) AS tempfile_count
FROM dba_tablespaces ts
LEFT JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY'
GROUP BY ts.tablespace_name;

-- For each tablespace returning tempfile_count = 0:
ALTER TABLESPACE <temp_ts_name>
  ADD TEMPFILE '/u01/oradata/orcl/<temp_ts_name>01.dbf'
  SIZE 4G AUTOEXTEND ON MAXSIZE 32G;

6. Handle Multiple Temporary Tablespaces in a Large Database

-- List all temporary tablespace groups and their members:
SELECT group_name, tablespace_name
FROM dba_tablespace_groups
ORDER BY group_name, tablespace_name;

-- Add tempfiles to all empty temp tablespaces in one block:
BEGIN
  FOR ts IN (
    SELECT ts.tablespace_name
    FROM dba_tablespaces ts
    LEFT JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
    WHERE ts.contents = 'TEMPORARY'
    GROUP BY ts.tablespace_name
    HAVING COUNT(tf.file_name) = 0
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLESPACE ' || ts.tablespace_name ||
      ' ADD TEMPFILE ''/u01/oradata/orcl/' || LOWER(ts.tablespace_name) || '01.dbf'' ' ||
      'SIZE 2G AUTOEXTEND ON MAXSIZE 32G';
    DBMS_OUTPUT.PUT_LINE('Added tempfile to: ' || ts.tablespace_name);
  END LOOP;
END;
/

Prevention Strategies

1. Include Tempfile Creation in Every Recovery Runbook

Since RMAN never restores tempfiles, every recovery runbook must include:

-- Step N: Recreate tempfiles (always required after recovery)
-- Run immediately after ALTER DATABASE OPEN or ALTER DATABASE OPEN RESETLOGS:
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 4G AUTOEXTEND ON;
-- Repeat for each temporary tablespace.

2. Include Tempfile Verification in Post-Startup Checks

-- Add to the post-startup monitoring checklist:
SELECT ts.tablespace_name, COUNT(tf.file_name) AS tempfile_count
FROM dba_tablespaces ts
LEFT JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY'
GROUP BY ts.tablespace_name
HAVING COUNT(tf.file_name) = 0;
-- Zero rows expected.

3. Set Up Autoextend on Tempfiles

-- Prevent the related ORA-01652 from occurring after ORA-25153 is fixed:
ALTER DATABASE TEMPFILE '/u01/oradata/temp01.dbf'
  AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

4. Size Based on Historical Usage

-- Use AWR data to right-size the tempfile:
SELECT
  ROUND(MAX(temp_space_allocated) / 1024 / 1024 / 1024 * 1.5, 1) AS recommended_size_gb
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 30;

Related Errors

• ORA-01652 - Unable to extend temp segment (temp tablespace full)
• ORA-01187 - Cannot read from file because it failed verification
• ORA-01110 - Data file identification

Emergency Response

Fastest Fix

-- Add a tempfile immediately:
ALTER TABLESPACE temp
  ADD TEMPFILE '/u01/oradata/temp01.dbf'
  SIZE 1G;  -- Minimal size to unblock sessions; increase later

-- Verify:
SELECT tablespace_name, file_name, status FROM dba_temp_files;

If the Temporary Tablespace Name Is Unknown

-- Find all empty temp tablespaces:
SELECT ts.tablespace_name
FROM dba_tablespaces ts
LEFT JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY'
GROUP BY ts.tablespace_name
HAVING COUNT(tf.file_name) = 0;

-- Add to each one found.

Post-Fix Validation

-- Confirm no empty temp tablespaces remain:
SELECT ts.tablespace_name, COUNT(tf.file_name) AS files
FROM dba_tablespaces ts
LEFT JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY'
GROUP BY ts.tablespace_name;
-- All rows should have files > 0.

-- Verify a sort operation works:
SELECT * FROM dba_objects ORDER BY object_name FETCH FIRST 10 ROWS ONLY;