🧩 How to Add a Tempfile to a Temporary Tablespace in Oracle Database

 🧩 How to Add a Tempfile to a Temporary Tablespace in Oracle Database

If your database reports “Unable to extend temp segment” or temporary tablespace is running out of space — it’s time to add a new tempfile!

Let’s see how to do it safely 👇

⚙️ Step 1: Check Current Tempfiles

Use this query to see existing tempfiles and their sizes:

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

FROM dba_temp_files;

💡 This helps you decide where and how much to add.

🧮 Step 2: Add a New Tempfile

Use the following command (adjust size and path as needed):

ALTER DATABASE TEMPFILE '/u01/oradata/ORA/temp02.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

✅ This adds a new tempfile to the temporary tablespace and allows it to autoextend.

🧠 Step 3: Confirm Addition

Run again:

SELECT file_name, bytes/1024/1024 AS size_mb 

FROM dba_temp_files;

You should now see the new tempfile listed.

🔧 Step 4: Monitor Usage

Check temp usage regularly with:

SELECT tablespace_name, SUM(bytes_used)/1024/1024 AS used_mb, 

SUM(bytes_free)/1024/1024 AS free_mb 

FROM v$temp_space_header 

GROUP BY tablespace_name;

💡 Monitoring helps prevent temp-related session errors.

🧠 DBA Tips

Always store tempfiles on high-speed disks for better performance

Use multiple tempfiles in RAC environments

Avoid deleting tempfiles while sessions are active

Keep autoextend enabled to handle sudden workload spikes

✨ 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