How to Drop and Recreate Temp Tablespace in Oracle (Step-by-Step Guide)

Drop and Recreate Temp Tablespace in Oracle is a crucial maintenance task for every DBA who wants to keep their database running efficiently. Over time, temporary tablespaces can become fragmented or corrupted, leading to slower performance or storage issues.

By learning how to drop and recreate the temp tablespace correctly, you can reclaim space, fix corruption, and ensure optimal database performance. This step-by-step guide will walk you through the exact SQL commands and best practices to perform this safely in your Oracle environment.

---

What Is a Temporary Tablespace in Oracle?

A temporary tablespace is used by Oracle Database to store intermediate data generated during operations like:

• Sorting large result sets
• Creating indexes
• Performing joins and aggregations
• Running complex queries

Each database has one default temporary tablespace, typically named TEMP.
If the temp tablespace becomes full, fragmented, or corrupt, you may encounter errors like:

ORA-25153: Temporary Tablespace is Empty
ORA-01110: Data file ... not found
ORA-01565: Error in identifying file

The solution? Drop and recreate the temp tablespace.

---

Steps to Drop and Recreate Temp Tablespace in Oracle

Below are the safe and efficient steps to drop and recreate the temporary tablespace in Oracle Database.

---

Step 1: Check the Existing Temporary Tablespace

Before making any changes, identify the current temp tablespace and its datafile location.
Use the following SQL command:

SELECT tablespace_name, file_name 
FROM dba_temp_files;

This query lists all existing temporary tablespaces and their associated tempfiles.
Typical output might look like:

TABLESPACE_NAME	FILE_NAME
TEMP	/u01/app/oradata/db/temp01.dbf

---

Step 2: Create a New Temporary Tablespace

Once you have identified the existing temp tablespace, the next step is to create a new temporary tablespace (for example, TEMP1).

CREATE TEMPORARY TABLESPACE TEMP1 
TEMPFILE '/u01/app/oradata/PRIM/temp01.dbf' SIZE 5G;

Explanation:

• TEMP1 is the new temp tablespace name.
• The TEMPFILE clause defines where the file will be created.
• SIZE 5G allocates 5 GB of initial space — you can adjust this based on your environment.

✅ Tip: Always create the new tempfile on a different disk or directory if you suspect the original file is corrupted or has I/O issues.

---

Step 3: Set the New Tablespace as the Default Temporary Tablespace

After creating the new tablespace, tell Oracle to start using it as the default temporary tablespace for all database sessions.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Why this is important:
Changing the default ensures that new sessions and operations automatically use the new temp tablespace (TEMP1) instead of the old one (TEMP).

This also allows you to safely drop the old tablespace later without impacting active sessions.

---

Step 4: Identify and Kill Sessions Using the Old Temp Tablespace

Before dropping the old temp tablespace, you must ensure no active sessions are using it.
Run the following query to check:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks,
       a.sid, a.serial#, a.username, a.osuser, a.status
FROM v$session a, v$sort_usage b
WHERE a.saddr = b.session_addr;

This query displays all sessions currently utilizing the temp space, along with session IDs.

Once identified, you can safely terminate those sessions using:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

Replace SID and SERIAL# with actual values from the query results.

✅ Tip:
Always confirm that killing sessions won’t disrupt critical business operations. Ideally, perform this during maintenance windows or off-peak hours.

---

Step 5: Drop the Old Temp Tablespace

Once all sessions are cleared and the new tablespace is active, you can safely drop the old temp tablespace using:

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Explanation:

• INCLUDING CONTENTS ensures Oracle removes all temporary objects inside the tablespace.
• AND DATAFILES deletes the associated tempfile from the OS automatically.

After this command, the old temp tablespace (TEMP) will be completely removed from your database.

---

Step 6: Verify the Change

Finally, confirm that the new temp tablespace is functioning correctly by running:

SELECT property_name, property_value 
FROM database_properties 
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

Expected output:

PROPERTY_NAME               PROPERTY_VALUE
--------------------------- ---------------
DEFAULT_TEMP_TABLESPACE     TEMP1

You can also verify the tempfile status:

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_temp_files;

If everything looks good, your new temp tablespace is ready for use.

---

Why Drop and Recreate Temp Tablespace?

Here are some common reasons DBAs choose to recreate the temp tablespace:

1. Corrupted tempfile — Oracle reports I/O or corruption errors.
2. Space fragmentation — Repeated large sorts leave fragmented free space.
3. Performance issues — Temp usage spikes due to inefficient sorting.
4. File relocation — Moving to a faster disk or new storage mount.

Dropping and recreating helps reclaim space, eliminate corruption, and optimize temp usage for better performance.

---

Best Practices for Managing Temp Tablespace

Regularly monitoring your tablespace helps prevent issues like fragmentation and temp file corruption. Check out this step-by-step article on how to monitor Oracle tablespace usage

• Monitor temp usage regularly:
  Use V$TEMPSEG_USAGE and V$SORT_USAGE views to track active temp usage.
• Use autoextend for flexibility:
  Create tempfiles with AUTOEXTEND ON to prevent “temp space full” errors during heavy loads. Example:

Example:

CREATE TEMPORARY TABLESPACE TEMP1 
TEMPFILE '/u01/app/oradata/PRIM/temp01.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

• Keep one dedicated temp tablespace per database:
  Avoid multiple temp tablespaces unless you have specialized workloads.
• Perform maintenance during low activity:
  Dropping or switching temp tablespaces can temporarily impact active queries.

Oracle recommends monitoring temporary tablespace usage and performance regularly. Refer to the Oracle Support Guide on Temporary Tablespace Management

---

Conclusion

Dropping and recreating the temp tablespace in Oracle is a simple yet powerful maintenance task that helps keep your database healthy and efficient.

By following these steps — check, create, switch, kill sessions, drop, and verify — you can ensure your database runs smoothly without space or corruption issues.

With the Oracle Temp Tablespace Recreation Process, you reclaim unused space, boost performance, and prevent potential I/O bottlenecks — all with just a few SQL commands.