How to Add a Temporary Tablespace in Oracle Standalone Database

🧱 How to Add a Temporary Tablespace in Oracle Standalone Database

Temporary tablespaces in Oracle are used for sorting operations, managing global temporary tables, and handling intermediate result sets. It's a good practice to add or manage these tablespaces especially in large databases or when the default temp tablespace gets full or fragmented.

In this guide, we'll walk you through a clean and safe method to create a new temporary tablespace, make it default (if needed), and drop the old one (optional).

---

🔧 Step-by-Step Instructions

1️⃣ Connect to the Database as SYSDBA

sqlplus / as sysdba
-- or
sqlplus sys@ORCL as sysdba

2️⃣ Identify Existing Temporary Tablespaces

SELECT tablespace_name, status, contents 
FROM dba_tablespaces 
WHERE contents = 'TEMPORARY';

3️⃣ Create a New Temporary Tablespace

CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_new01.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

💡 Tip: Adjust the TEMPFILE path and sizing parameters based on your filesystem and usage needs.

4️⃣ Make the New Temp Tablespace Default (Optional)

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

5️⃣ Verify the Default Temporary Tablespace

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

6️⃣ Drop the Old Temp Tablespace (Optional)

DROP TABLESPACE temp_old INCLUDING CONTENTS AND DATAFILES;

⚠️ Caution: Ensure the tablespace is no longer in use and is not the default before dropping it.

---

🧠 Why Manage Temporary Tablespaces?

• Improve performance during large sorts and index creations.
• Prevent "Temp Segment Full" errors by allocating enough space.
• Enable better segregation for different types of workloads.

🚨 Best Practices

• Always use EXTENT MANAGEMENT LOCAL and UNIFORM SIZE for better management.
• Monitor temp usage with v$tempseg_usage and dba_temp_files.
• Use AUTOEXTEND carefully to avoid unexpected filesystem usage spikes.

---

🎯 This process ensures your Oracle database handles temporary operations efficiently, helping prevent common space-related issues during sorts, joins, and global temp table operations.