🧩 How to Drop and Recreate a Temporary Tablespace in Oracle Database

 🧩 How to Drop and Recreate a Temporary Tablespace in Oracle Database

Sometimes the temporary tablespace becomes corrupted or needs cleanup due to space management issues. In that case, it’s safer to drop and recreate it properly. Let’s go step-by-step 👇

⚙️ Step 1: Create a New Temporary Tablespace

Before dropping the old one, create a new temp tablespace to keep the database operational:

CREATE TEMPORARY TABLESPACE temp_new 

TEMPFILE '/u01/oradata/ORA/temp_new01.dbf' SIZE 2G 

AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

✅ This creates a clean temporary tablespace ready to use.

⚙️ Step 2: Make It the Default

Assign the new tablespace as the database default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

💡 This ensures all new sessions will use the new temporary tablespace.

⚙️ Step 3: Drop the Old One

Once no sessions are using the old temp tablespace, drop it safely:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

⚠️ Be sure no active sessions are using the old temp tablespace before running this command.

🧠 Step 4: Verify

Check the current default temp tablespace:

SELECT property_name, property_value 

FROM database_properties 

WHERE property_name LIKE 'DEFAULT_TEMP_TABLESPACE';

🧠 DBA Tips

Always create a new temp tablespace before dropping the old one

Keep at least one temp tablespace in your database at all times

In large environments, create multiple tempfiles to improve parallel operations

Don’t forget to monitor temp usage after recreation

✨ Like & Follow my page for more Oracle DBA tips and tutorials:

👉 Facebook: https://www.facebook.com/people/Oracle-On-Linux-Tips/61581062542160/

📘 Blog: https://oracleinlinuxbyabosalma.blogspot.com