Resolving TEMP Tablespace Issues in Oracle: A Step-by-Step Guide by Talah Ahmed Shaikh

Recently, I encountered a challenging issue with the TEMP tablespace in Oracle, where the space wasn't being released as expected. After thorough analysis and troubleshooting, we were able to resolve it. Here's a simplified step-by-step process to help anyone facing a similar problem.

Steps to Resolve TEMP Tablespace Issues

Step 1: Check TEMP Tablespace Usage

Start by checking the TEMP tablespace's total, used, and free space:

SELECT 
 TABLESPACE_NAME, 
 ROUND(SUM(TABLESPACE_SIZE) / 1024 / 1024, 2) AS TOTAL_SIZE_MB, 
 ROUND(SUM(ALLOCATED_SPACE) / 1024 / 1024, 2) AS USED_SPACE_MB, 
 ROUND((SUM(TABLESPACE_SIZE) - SUM(ALLOCATED_SPACE)) / 1024 / 1024, 2) AS FREE_SPACE_MB
FROM 
 DBA_TEMP_FREE_SPACE
GROUP BY 
 TABLESPACE_NAME;
        

Example Output:

• Total Size: 30 GB
• Allocated Space: 29 GB
• Free Space: 1 GB

Step 2: Attempt to Release TEMP Space

Run the following command to release TEMP space:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL 4';
        

If this doesn’t work, proceed to the next step.

Step 3: Investigate the Alert Log

Check the alert log to identify the root cause. In our case, we found:

• The TEMP tablespace was dropped using this command:DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
• The TEMP datafiles (/path/temp1.dbf, /path/temp2.dbf) were deleted and recreated on the same day.

Step 4: Validate TEMP File Changes

Query the TEMP datafiles to check their details and segment number (TS#):

SELECT FILE#, CREATION_TIME, NAME, BYTES / 1024 / 1024 AS SIZE_MB, TS# 
FROM V$TEMPFILE;
        

Observation: The segment number (TS#) had changed from 4 to 36.

Step 5: Update the Query to Release TEMP Space

Since the TS# had changed, we modified the query to:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL 37';
        

(Note: TS# + 1 = 36 + 1 = 37)

Step 6: Verify TEMP Space Release

After executing the updated query, the TEMP space was successfully released. To confirm, recheck the TEMP usage:

SELECT 
 TABLESPACE_NAME, 
 ROUND(SUM(TABLESPACE_SIZE) / 1024 / 1024, 2) AS TOTAL_SIZE_MB, 
 ROUND(SUM(ALLOCATED_SPACE) / 1024 / 1024, 2) AS USED_SPACE_MB, 
 ROUND((SUM(TABLESPACE_SIZE) - SUM(ALLOCATED_SPACE)) / 1024 / 1024, 2) AS FREE_SPACE_MB
FROM 
 DBA_TEMP_FREE_SPACE
GROUP BY 
 TABLESPACE_NAME;
        

Key Takeaways:

1. Always verify TEMP tablespace properties (V$TEMPFILE) after datafile modifications.
2. Pay close attention to the segment number (TS#), which might change after recreating datafiles.
3. Increment the TS# (+1) when the default release command doesn't work.

This approach helped us resolve the issue quickly and efficiently. I hope this serves as a helpful guide for anyone troubleshooting TEMP tablespace issues in Oracle databases!