How to Check TEMP Usage in Oracle Database

W3Buddy

February 15, 2026 · 10 min read · 0

Add W3Buddy as a preferred source on Google

Article Summary

Learn how to check TEMP usage in Oracle Database with comprehensive SQL queries and formatting. Monitor tablespace usage, identify top consumers, and prevent performance issues with step-by-step guide.

Temporary tablespace management is a critical aspect of Oracle Database administration. When TEMP tablespace fills up, it can cause queries to fail, sessions to hang, and overall database performance to degrade. In this guide, we’ll explore various SQL queries that help you monitor and troubleshoot TEMP tablespace usage effectively, with proper output formatting for better readability.

Understanding TEMP Tablespace

The TEMP tablespace in Oracle is used for temporary operations such as sorting, hash joins, index creation, and other operations that require temporary storage. Unlike permanent tablespaces, data in TEMP is transient and doesn’t need to be backed up. However, monitoring its usage is essential to prevent performance bottlenecks.

Checking Overall TEMP Space Usage

The most straightforward way to check your TEMP tablespace usage is with this comprehensive query:

SET LINESIZE 200
SET PAGESIZE 100
COLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'
COLUMN "Total Size [GB]" FORMAT 999,990.99
COLUMN "Used_size[GB]" FORMAT 999,990.99
COLUMN "Free_size[GB]" FORMAT 999,990.99
COLUMN "Used Percentage" FORMAT 990.99

SELECT a.tablespace_name,
       ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2) "Total Size [GB]",
       ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
       ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
       ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,
     dba_tablespaces b,
     (SELECT tablespace_name, SUM(blocks) total_blocks 
      FROM dba_temp_files 
      GROUP BY tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name 
  AND a.tablespace_name=c.tablespace_name;

This query provides a complete overview showing total size, used space, free space, and usage percentage in gigabytes with properly aligned columns.

Calculating TEMP Usage Percentage

For a quick percentage check of your TEMP tablespace, use this simple query:

SET LINESIZE 100
SET PAGESIZE 50
COLUMN "percent used" FORMAT 990.99 HEADING 'TEMP Usage %'

SELECT (s.tot_used_blocks/f.total_blocks)*100 AS "percent used"
FROM (SELECT SUM(used_blocks) tot_used_blocks
      FROM v$sort_segment 
      WHERE tablespace_name='TEMP') s,
     (SELECT SUM(blocks) total_blocks
      FROM dba_temp_files 
      WHERE tablespace_name='TEMP') f;

This gives you an immediate view of how much of your TEMP tablespace is currently allocated.

Identifying Top TEMP Consumers

When TEMP usage spikes, you need to quickly identify which sessions are consuming the most space. This query shows the top 10 sessions by TEMP usage:

SET LINESIZE 250
SET PAGESIZE 100
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN sid FORMAT 9999 HEADING 'SID'
COLUMN serial# FORMAT 999999 HEADING 'Serial#'
COLUMN program FORMAT A25 HEADING 'Program'
COLUMN module FORMAT A20 HEADING 'Module'
COLUMN action FORMAT A15 HEADING 'Action'
COLUMN "DB Username" FORMAT A15 HEADING 'DB User'
COLUMN osuser FORMAT A15 HEADING 'OS User'
COLUMN "Used MB" FORMAT 999,999.99 HEADING 'TEMP Used (MB)'
COLUMN sql_text FORMAT A50 HEADING 'SQL Text' WORD_WRAPPED

SELECT * FROM
(SELECT d.tablespace_name,
        a.sid,
        a.serial#,
        a.program,
        a.module,
        a.action,
        a.username "DB Username",
        a.osuser,
        ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",
        c.sql_text
FROM v$session a, 
     v$tempseg_usage b, 
     v$sqlarea c,
     dba_tablespaces d
WHERE a.saddr = b.session_addr 
  AND c.address= a.sql_address 
  AND c.hash_value = a.sql_hash_value 
  AND d.tablespace_name=b.tablespace 
ORDER BY b.tablespace, b.blocks DESC)
WHERE rownum <=10;

This query is invaluable during troubleshooting, as it shows you not only who is using TEMP space but also what SQL they’re running.

Monitoring Active Sessions Using TEMP

To see which sessions are currently consuming significant TEMP space (over 1GB in this example), use:

SET LINESIZE 250
SET PAGESIZE 100
COLUMN sysdate FORMAT A20 HEADING 'Current Time'
COLUMN username FORMAT A15 HEADING 'Database User'
COLUMN sid FORMAT 9999 HEADING 'SID'
COLUMN serial# FORMAT 999999 HEADING 'Serial#'
COLUMN osuser FORMAT A15 HEADING 'OS User'
COLUMN mb_used FORMAT 999,999.99 HEADING 'TEMP Used (MB)'
COLUMN sql_text FORMAT A60 HEADING 'SQL Statement' WORD_WRAPPED

SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') sysdate,
       a.username, 
       a.sid, 
       a.serial#, 
       a.osuser, 
       (b.blocks*d.block_size)/1048576 mb_used, 
       c.sql_text
FROM v$session a, 
     v$tempseg_usage b, 
     v$sqlarea c,
     (SELECT block_size FROM dba_tablespaces WHERE tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
  AND a.saddr = b.session_addr
  AND c.address= a.sql_address
  AND c.hash_value = a.sql_hash_value
  AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY mb_used DESC;

This helps you focus on sessions that are heavy TEMP users and may need investigation or termination.

Checking Sort Segment Usage by User

To see which users are performing operations that require TEMP space:

SET LINESIZE 180
SET PAGESIZE 100
COLUMN username FORMAT A15 HEADING 'Username'
COLUMN sid FORMAT 9999 HEADING 'SID'
COLUMN serial# FORMAT 999999 HEADING 'Serial#'
COLUMN tablespace FORMAT A15 HEADING 'Tablespace'
COLUMN contents FORMAT A10 HEADING 'Contents'
COLUMN extents FORMAT 999,999 HEADING 'Extents'
COLUMN blocks FORMAT 999,999,999 HEADING 'Blocks Used'

SELECT s.username,
       s.sid,
       s.serial#,
       u.tablespace, 
       u.contents, 
       u.extents, 
       u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
ORDER BY u.blocks DESC;

Viewing Free Space in TEMP Tablespace

To understand how much free space remains in your TEMP tablespace:

SET LINESIZE 150
SET PAGESIZE 50
COLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'
COLUMN FreeSpaceInGB FORMAT 999,990.99 HEADING 'Free Space (GB)'
COLUMN UsedSpaceInGB FORMAT 999,990.99 HEADING 'Used Space (GB)'
COLUMN TotalSpaceInGB FORMAT 999,990.99 HEADING 'Total Space (GB)'

SELECT tablespace_name,
       (free_blocks*8)/1024/1024 FreeSpaceInGB,
       (used_blocks*8)/1024/1024 UsedSpaceInGB,
       (total_blocks*8)/1024/1024 TotalSpaceInGB
FROM v$sort_segment 
WHERE tablespace_name LIKE '%TEMP%';

Comprehensive TEMP Status Query

For a detailed view including extent management information:

SET LINESIZE 180
SET PAGESIZE 100
COLUMN "Status" FORMAT A10 HEADING 'Status'
COLUMN "Name" FORMAT A20 HEADING 'Tablespace Name'
COLUMN "Type" FORMAT A12 HEADING 'Type'
COLUMN "ExtManag" FORMAT A10 HEADING 'Extent Mgmt'
COLUMN "Size (M)" FORMAT A20 HEADING 'Size (MB)'
COLUMN "Used (M)" FORMAT A40 HEADING 'Used/Total (MB)'
COLUMN "Used %" FORMAT A10 HEADING 'Used %'

SELECT d.status "Status", 
       d.tablespace_name "Name", 
       d.contents "Type", 
       d.extent_management "ExtManag",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
       TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999,999.999') ||'/'||
       TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, 
     (SELECT tablespace_name, SUM(bytes) bytes 
      FROM dba_temp_files 
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes_cached) bytes 
      FROM v$temp_extent_pool 
      GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) 
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management LIKE 'LOCAL' 
  AND d.contents LIKE 'TEMPORARY';

Advanced: Top 10 Sessions with Largest TEMP Usage

For detailed session-level analysis with timing information:

SET LINESIZE 250
SET PAGESIZE 100
COLUMN sid FORMAT 9999 HEADING 'SID'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN sesshash FORMAT 9999999999 HEADING 'Session Hash'
COLUMN sorthash FORMAT 9999999999 HEADING 'Sort Hash'
COLUMN username FORMAT A15 HEADING 'Username'
COLUMN tablespace FORMAT A15 HEADING 'Tablespace'
COLUMN mbused FORMAT 999,999.99 HEADING 'MB Used'
COLUMN noexts FORMAT 999,999 HEADING 'Extents'
COLUMN proginfo FORMAT A30 HEADING 'Program/Module' TRUNCATE
COLUMN lastcallet FORMAT A15 HEADING 'Last Call ET'

SELECT * FROM (
    SELECT s.sid,
           s.status,
           s.sql_hash_value sesshash,
           u.SQLHASH sorthash,
           s.username,
           u.tablespace,
           SUM(u.blocks*p.value/1024/1024) mbused,
           SUM(u.extents) noexts,
           NVL(s.module,s.program) proginfo,
           FLOOR(last_call_et/3600)||':'||
           FLOOR(MOD(last_call_et,3600)/60)||':'||
           MOD(MOD(last_call_et,3600),60) lastcallet
    FROM v$sort_usage u,
         v$session s,
         v$parameter p
    WHERE u.session_addr = s.saddr
      AND p.name = 'db_block_size'
    GROUP BY s.sid, s.status, s.sql_hash_value, u.sqlhash, 
             s.username, u.tablespace, NVL(s.module,s.program),
             FLOOR(last_call_et/3600)||':'||
             FLOOR(MOD(last_call_et,3600)/60)||':'||
             MOD(MOD(last_call_et,3600),60)
    ORDER BY 7 DESC, 3
)
WHERE rownum < 11;

Quick TEMP Size Query

For a simple size check:

SET LINESIZE 100
SET PAGESIZE 50
COLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'
COLUMN mb FORMAT 999,999.99 HEADING 'Size (MB)'

SELECT tablespace_name, 
       SUM(bytes)/1024/1024 mb
FROM dba_temp_files
GROUP BY tablespace_name;

Best Practices for TEMP Tablespace Management

Regular Monitoring: Schedule these queries to run regularly, especially the percentage usage and top consumers queries. Set up alerts when usage exceeds 80%.

Understand Your Workload: Know which operations in your database typically use TEMP space heavily. Batch jobs, reporting queries, and data warehouse operations are common culprits.

Size Appropriately: Ensure your TEMP tablespace is sized adequately for your workload. A general rule of thumb is 10-20% of your database size, but this varies greatly by application.

Investigate Long-Running Queries: Sessions that hold TEMP space for extended periods may indicate poorly optimized SQL that needs tuning.

Consider Multiple TEMP Tablespaces: For very large databases, consider creating multiple temporary tablespaces to distribute load and reduce contention.

Creating a Monitoring Script

You can combine these queries into a single monitoring script for regular execution:

-- TEMP Tablespace Monitoring Script
-- Run this script for comprehensive TEMP usage analysis

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET TIMING OFF

PROMPT 
PROMPT ========================================
PROMPT   TEMP TABLESPACE USAGE REPORT
PROMPT ========================================
PROMPT 
PROMPT Report Generated: 
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
PROMPT 

PROMPT 
PROMPT ========================================
PROMPT   1. Overall TEMP Usage Summary
PROMPT ========================================
PROMPT 

SET LINESIZE 150
SET PAGESIZE 100
COLUMN tablespace_name FORMAT A20 HEADING 'Tablespace'
COLUMN "Total Size [GB]" FORMAT 999,990.99
COLUMN "Used_size[GB]" FORMAT 999,990.99
COLUMN "Free_size[GB]" FORMAT 999,990.99
COLUMN "Used Percentage" FORMAT 990.99

SELECT a.tablespace_name,
       ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2) "Total Size [GB]",
       ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
       ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
       ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,
     dba_tablespaces b,
     (SELECT tablespace_name, SUM(blocks) total_blocks 
      FROM dba_temp_files 
      GROUP BY tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name 
  AND a.tablespace_name=c.tablespace_name;

PROMPT 
PROMPT ========================================
PROMPT   2. Top 10 TEMP Consumers
PROMPT ========================================
PROMPT 

SET LINESIZE 250
COLUMN sid FORMAT 9999 HEADING 'SID'
COLUMN serial# FORMAT 999999 HEADING 'Serial#'
COLUMN "DB Username" FORMAT A15 HEADING 'DB User'
COLUMN osuser FORMAT A12 HEADING 'OS User'
COLUMN "Used MB" FORMAT 999,999.99 HEADING 'TEMP (MB)'
COLUMN program FORMAT A20 HEADING 'Program' TRUNCATE

SELECT * FROM
(SELECT d.tablespace_name,
        a.sid,
        a.serial#,
        a.username "DB Username",
        a.osuser,
        a.program,
        ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB"
FROM v$session a, 
     v$tempseg_usage b, 
     dba_tablespaces d
WHERE a.saddr = b.session_addr 
  AND d.tablespace_name=b.tablespace 
ORDER BY b.blocks DESC)
WHERE rownum <=10;

PROMPT 
PROMPT ========================================
PROMPT   End of Report
PROMPT ========================================

SET FEEDBACK ON
SET VERIFY ON

Conclusion

Monitoring TEMP tablespace usage is essential for maintaining a healthy Oracle database. The queries provided in this guide, with proper formatting directives, give you multiple perspectives on TEMP usage from high-level percentage views to detailed session-level analysis. The formatting commands (SET LINESIZE, SET PAGESIZE, COLUMN) ensure the output is readable and professional, making it easier to identify issues quickly.

By regularly running these queries and understanding the patterns in your database, you can proactively manage TEMP space and prevent performance issues before they impact your users. Remember, when you identify sessions consuming excessive TEMP space, always investigate the SQL being executed first. Often, the solution is query optimization rather than simply adding more TEMP space.

Was this helpful?

Written by

W3buddy

Explore W3Buddy for in-depth guides, breaking tech news, and expert analysis on AI, cybersecurity, databases, web development, and emerging technologies.