Oracle DBA

In Oracle Database, temporary tablespaces play a critical role during SQL execution. Whenever a query involves sorting, joining, aggregation, index creation, or operations that cannot be handled entirely in memory (PGA), Oracle uses temporary tablespaces to store intermediate results.

As a DBA, understanding how to monitor and troubleshoot temporary tablespace usage is essential to avoid common errors such as ORA-01652: unable to extend temp segment. This article explains the concepts, provides monitoring queries, and walks through multiple troubleshooting scenarios with practical solutions.

What is a Temporary Tablespace?

A temporary tablespace is a special type of tablespace that holds transient data generated during query processing. Unlike permanent tablespaces:

• Data in temporary tablespaces is not logged in redo.
• Segments are allocated and dropped as needed.
• Space is released when the transaction or session completes.

Every database must have at least one temporary tablespace. In multitenant databases, each PDB can have its own temporary tablespace.

Creating and Assigning Temporary Tablespaces

You can create a temporary tablespace as follows:

CREATE TEMPORARY TABLESPACE temp_tbs1
TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_tbs1_01.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Assign it to a user:

ALTER USER hr TEMPORARY TABLESPACE temp_tbs1;

To see the default temporary tablespace for the database:

SELECT property_value 
FROM database_properties 
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

Monitoring Temporary Tablespace Usage

Oracle provides multiple views to monitor usage of temporary space:

1. Current Temporary Usage by Session

SELECT s.sid, s.serial#, s.username,
       t.tablespace, t.segfile#, t.segblk#, t.blocks*8/1024 AS mb_used
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
ORDER BY mb_used DESC;

This shows how much each session is consuming in the temporary tablespace.

2. Temporary Tablespace Utilization Summary

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024,2) AS allocated_mb,
       ROUND(SUM(free_blocks*block_size)/1024/1024,2) AS free_mb
FROM v$sort_segment
GROUP BY tablespace_name;

This provides an overview of allocated vs. free space in each temporary tablespace.

3. High Watermark of Temp Usage

SELECT tablespace_name,
       total_blocks*block_size/1024/1024 AS total_mb,
       used_blocks*block_size/1024/1024 AS used_mb,
       free_blocks*block_size/1024/1024 AS free_mb,
       max_used_blocks*block_size/1024/1024 AS max_used_mb
FROM v$sort_segment;

The MAX_USED_BLOCKS column shows the peak usage since the database started.

Troubleshooting Scenarios

Scenario 1: ORA-01652 Unable to Extend Temp Segment

Error:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP  

Cause: A query requested more temporary space than available.

Resolution:

1. Add or resize tempfile: ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' RESIZE 2G; ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORCL/temp02.dbf' ADD SIZE 1G;
2. Set AUTOEXTEND for tempfiles: ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
3. Optimize SQL to reduce sort area usage.

Scenario 2: One Session Consuming All Temp Space

You may find a session running a large sort or cartesian join consuming gigabytes of temporary space.

Detection:

SELECT s.sid, s.serial#, s.username, p.spid,
       t.blocks*8/1024 AS mb_used, s.sql_id, q.sql_text
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
JOIN v$process p ON s.paddr = p.addr
JOIN v$sql q ON s.sql_id = q.sql_id
ORDER BY mb_used DESC;

Resolution:

• Kill the offending session if it impacts production: ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
• Advise developers to rewrite queries using indexes or better join conditions.

Scenario 3: Multiple Sessions Contending for Temp

Heavy parallel operations can exhaust TEMP.

Monitoring:

SELECT inst_id, tablespace, COUNT(*) AS active_temp_segments,
       SUM(blocks)*8/1024 AS mb_used
FROM gv$sort_usage
GROUP BY inst_id, tablespace;

Resolution:

• Create multiple temporary tablespaces and assign them to different users: CREATE TEMPORARY TABLESPACE temp_tbs2 TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_tbs2_01.dbf' SIZE 500M AUTOEXTEND ON; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_tbs2;
• In RAC, ensure each instance has enough tempfiles.

Scenario 4: Temp Segment Not Shrinking

Even after heavy queries finish, TEMP may appear large.

Explanation:
Tempfiles do not shrink automatically; Oracle reuses allocated extents.

Resolution:

• To manually shrink: ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' RESIZE 1G;
• Better approach: allow Oracle to reuse space and monitor high-water marks instead of resizing frequently.

Scenario 5: Temporary Tablespace Group

For balancing workload, you can create a group of temp tablespaces.

CREATE TEMPORARY TABLESPACE temp_tbs3
TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_tbs3_01.dbf' SIZE 1G;

ALTER DATABASE TEMPORARY TABLESPACE temp_tbs1 TABLESPACE GROUP temp_grp;
ALTER DATABASE TEMPORARY TABLESPACE temp_tbs3 TABLESPACE GROUP temp_grp;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;

Oracle will spread sessions across the group members for better scalability.

Best Practices

1. Always enable AUTOEXTEND on tempfiles with a reasonable MAXSIZE.
2. Use multiple tempfiles to reduce contention.
3. Monitor usage regularly with V$SORT_USAGE and V$SORT_SEGMENT.
4. Use temporary tablespace groups for large environments.
5. Avoid frequent manual shrinking; rely on Oracle reuse mechanism.
6. In RAC, ensure uniform tempfiles across all instances.
7. Size PGA adequately to reduce spillover to TEMP.

Conclusion

Temporary tablespaces are essential for efficient SQL execution in Oracle. Proper monitoring with views such as V$SORT_USAGE, V$SORT_SEGMENT, and GV$SORT_USAGE allows DBAs to detect space bottlenecks and take corrective action. By sizing appropriately, enabling autoextend, and leveraging temporary tablespace groups, you can ensure smooth query execution and prevent runtime errors like ORA-01652.