🧩 How to Resize a Temporary Tablespace in Oracle Database

 🧩 How to Resize a Temporary Tablespace in Oracle Database

Sometimes the temporary tablespace grows too large due to heavy sorts or joins. To reclaim space or adjust performance, you can resize or shrink it safely. Let’s see how 👇

⚙️ Step 1: Check Current Tempfiles

Before resizing, view the current tempfile sizes:

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_MB 

FROM dba_temp_files;

⚙️ Step 2: Resize the Tempfile

Use the ALTER DATABASE command to reduce or increase the tempfile size:

ALTER DATABASE TEMPFILE '/u01/oradata/ORA/temp01.dbf' RESIZE 2G;

💡 You can resize only if the new size is larger than the space currently in use.

⚙️ Step 3: Add a New Tempfile (Optional)

If you need to spread the load across multiple disks:

ALTER DATABASE ADD TEMPFILE '/u01/oradata/ORA/temp02.dbf' SIZE 2G AUTOEXTEND ON;

✅ This helps improve performance for large sorts or parallel queries.

⚙️ Step 4: Drop an Extra Tempfile (Optional)

If one tempfile isn’t needed anymore:

ALTER DATABASE TEMPFILE '/u01/oradata/ORA/temp02.dbf' DROP INCLUDING DATAFILES;

🧠 DBA Tips

Always check active sessions before resizing the temp tablespace

Avoid resizing during peak usage hours

Consider using AUTOEXTEND for flexibility

Monitor temp usage using V$TEMPSEG_USAGE

✨ 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