V$SORT_USAGE - Find Sessions Using Temp Tablespace

V$SORT_USAGE

Overview

V$SORT_USAGE (also accessible as V$TEMPSEG_USAGE in Oracle 11g and later) displays one row for each temporary segment currently allocated in the temporary tablespace. Every time a session performs an operation that cannot be completed entirely in memory — a sort, a hash join, a bitmap merge, a group-by, or a global temporary table write — Oracle allocates extents in the temporary tablespace and records them here. The view is the primary diagnostic tool when ORA-01652 (unable to extend temp segment) fires, when the DBA needs to find which session is consuming the most temp space, or when tuning PGA memory to reduce spill-to-disk operations.

V$SORT_USAGE only shows active temporary segments. Once a session completes the operation causing the allocation, the row disappears. For point-in-time snapshots of historical temp usage, query DBA_HIST_ACTIVE_SESS_HISTORY and look at TEMP_SPACE_ALLOCATED.

View Type: Dynamic Performance View Available Since: Oracle 8i (V$TEMPSEG_USAGE alias added in Oracle 11g) Required Privileges: SELECT on V_$SORT_USAGE or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
USERNAME	VARCHAR2(30)	Oracle username of the session using the temp segment; may be NULL for some internal operations
SESSION_ADDR	RAW(8)	Address of the session owning this temp segment; joins to V$SESSION.SADDR
SESSION_NUM	NUMBER	Session serial number; use with SESSION_ADDR to uniquely identify the session across reconnections
SQLADDR	RAW(8)	Address of the SQL statement responsible for the temp allocation
SQLHASH	NUMBER	Hash value of the SQL statement; can be joined to V$SQLAREA.HASH_VALUE
SQL_ID	VARCHAR2(13)	SQL_ID of the statement driving the temp allocation; join to V$SQL for full text
TABLESPACE	VARCHAR2(31)	Name of the temporary tablespace in use
CONTENTS	VARCHAR2(9)	TEMPORARY for normal temp segments, PERMANENT for session-level segments
SEGTYPE	VARCHAR2(9)	Operation type causing the allocation: SORT, HASH, DATA, INDEX, LOB_DATA, or LOB_INDEX
EXTENTS	NUMBER	Number of extents currently allocated to this temp segment
BLOCKS	NUMBER	Number of database blocks allocated (EXTENTS * blocks_per_extent)
SEGRFNO#	NUMBER	Relative file number of the temporary file containing this segment
SEGBLK#	NUMBER	Starting block number of the segment within the temporary file

Essential Queries

Basic Usage

List all active temporary segments with the owning session details:

SELECT
    su.username,
    su.tablespace,
    su.segtype,
    su.extents,
    su.blocks,
    ROUND(su.blocks * (
        SELECT value FROM v$parameter WHERE name = 'db_block_size'
    ) / 1024 / 1024, 2)                            AS temp_mb,
    su.sql_id,
    s.sid,
    s.serial#,
    s.status,
    s.machine,
    s.program,
    s.module
FROM
    v$sort_usage su
    JOIN v$session s ON s.saddr = su.session_addr
ORDER BY
    su.blocks DESC;

Top Temp Consumers

Rank sessions by total temp space allocated, aggregating across multiple concurrent operations per session:

SELECT
    su.username,
    s.sid,
    s.serial#,
    s.status,
    s.machine,
    s.program,
    s.module,
    s.sql_id,
    COUNT(*)                                        AS temp_segments,
    SUM(su.extents)                                 AS total_extents,
    SUM(su.blocks)                                  AS total_blocks,
    ROUND(SUM(su.blocks) * b.block_size / 1024 / 1024, 2) AS total_temp_mb
FROM
    v$sort_usage    su
    JOIN v$session      s ON s.saddr = su.session_addr
    CROSS JOIN (SELECT TO_NUMBER(value) AS block_size
                FROM   v$parameter
                WHERE  name = 'db_block_size') b
GROUP BY
    su.username,
    s.sid,
    s.serial#,
    s.status,
    s.machine,
    s.program,
    s.module,
    s.sql_id,
    b.block_size
ORDER BY
    total_temp_mb DESC;

Temp Usage with SQL Text

Join V$SORT_USAGE to V$SQL to show the exact SQL statement driving each temp allocation — essential for identifying the query to tune or kill:

SELECT
    su.username,
    s.sid,
    s.serial#,
    su.tablespace,
    su.segtype,
    ROUND(su.blocks * p.block_size / 1024 / 1024, 2) AS temp_mb,
    su.sql_id,
    s.event,
    s.seconds_in_wait                               AS secs_waiting,
    SUBSTR(q.sql_text, 1, 200)                      AS sql_text
FROM
    v$sort_usage    su
    JOIN v$session      s ON s.saddr = su.session_addr
    LEFT JOIN v$sql         q ON q.sql_id = su.sql_id
    CROSS JOIN (SELECT TO_NUMBER(value) AS block_size
                FROM   v$parameter
                WHERE  name = 'db_block_size') p
ORDER BY
    su.blocks DESC;

Temp Tablespace Capacity vs Active Usage

Compare total temp tablespace capacity against what is currently allocated, to assess how close to exhaustion the instance is:

SELECT
    tf.tablespace_name,
    ROUND(SUM(tf.bytes) / 1024 / 1024 / 1024, 2)       AS total_temp_gb,
    ROUND(NVL(SUM(su.blocks) * p.block_size, 0)
          / 1024 / 1024 / 1024, 2)                      AS used_temp_gb,
    ROUND((SUM(tf.bytes) - NVL(SUM(su.blocks) * p.block_size, 0))
          / 1024 / 1024 / 1024, 2)                      AS free_temp_gb,
    ROUND(NVL(SUM(su.blocks) * p.block_size, 0)
          / SUM(tf.bytes) * 100, 1)                     AS pct_used
FROM
    dba_temp_files tf
    LEFT JOIN v$sort_usage su ON su.tablespace = tf.tablespace_name
    CROSS JOIN (SELECT TO_NUMBER(value) AS block_size
                FROM   v$parameter
                WHERE  name = 'db_block_size') p
GROUP BY
    tf.tablespace_name,
    p.block_size
ORDER BY
    tf.tablespace_name;

Operation Type Breakdown

Understand whether temp consumption is driven by sorts, hash joins, or other operations — guides PGA_AGGREGATE_TARGET tuning:

SELECT
    su.segtype,
    COUNT(*)                                        AS segment_count,
    COUNT(DISTINCT su.session_addr)                 AS session_count,
    SUM(su.extents)                                 AS total_extents,
    SUM(su.blocks)                                  AS total_blocks,
    ROUND(SUM(su.blocks) * p.block_size / 1024 / 1024, 2) AS total_mb,
    ROUND(AVG(su.blocks) * p.block_size / 1024 / 1024, 2) AS avg_mb_per_segment,
    ROUND(MAX(su.blocks) * p.block_size / 1024 / 1024, 2) AS max_mb_single_segment
FROM
    v$sort_usage su
    CROSS JOIN (SELECT TO_NUMBER(value) AS block_size
                FROM   v$parameter
                WHERE  name = 'db_block_size') p
GROUP BY
    su.segtype,
    p.block_size
ORDER BY
    total_mb DESC;

Common Use Cases

• ORA-01652 root cause identification — When the error fires, immediately query V$SORT_USAGE sorted by blocks DESC to find the session(s) consuming the most temp space; then retrieve their SQL_ID to examine the execution plan and decide whether to kill the session or add temp space
• PGA_AGGREGATE_TARGET tuning — A high proportion of SORT and HASH segtype entries indicates that sort operations are spilling to disk; increasing PGA_AGGREGATE_TARGET (or WORKAREA_SIZE_POLICY) allows more of these operations to complete in memory, reducing temp usage
• Hash join spill investigation — Segtype = ‘HASH’ entries from a specific SQL_ID indicate the hash join is too large for memory; review the execution plan in V$SQL_PLAN and consider partition-wise joins or hash join hints to reduce the in-memory footprint
• Global temporary table monitoring — Segtype = ‘DATA’ or ‘INDEX’ entries that persist across multiple sessions may indicate heavy use of global temporary tables; confirm with DBA_SEGMENTS for permanent segment variants
• Temp tablespace capacity planning — Running the capacity vs usage query at peak load times (e.g., nightly batch windows) provides the baseline for sizing temp tablespace; add 20-30% headroom above the observed peak
• Identifying queries to tune — Chronic temp consumers are strong candidates for SQL tuning: adding appropriate indexes, rewriting correlated subqueries as joins, or introducing result cache can eliminate the sort/hash operation entirely

Related Views

• V$SESSION — Join on SADDR to get full session details including username, machine, program, and wait events for the temp-consuming session
• V$SQL — Join on SQL_ID to retrieve the full SQL text and execution statistics for the statement causing the temp allocation
• V$ACTIVE_SESSION_HISTORY — Historical temp usage sampling; TEMP_SPACE_ALLOCATED column available in Oracle 12c and later
• V$TEMPSEG_USAGE — Synonym for V$SORT_USAGE introduced in Oracle 11g; identical columns, interchangeable in queries
• DBA_TEMP_FILES — Lists the physical temp files and their sizes; join on tablespace name to cross-check capacity against V$SORT_USAGE consumption
• V$PGA_TARGET_ADVICE — Provides PGA sizing recommendations; reducing sort/hash spills shown in V$SORT_USAGE is often the key benefit of increasing PGA_AGGREGATE_TARGET

Version Notes

• Oracle 8i: V$SORT_USAGE introduced with columns SESSION_ADDR, SEGTYPE, BLOCKS, EXTENTS
• Oracle 10g: SQL_ID column added, replacing the less reliable SQLHASH/SQLADDR approach for SQL identification
• Oracle 11g: V$TEMPSEG_USAGE alias introduced as a clearer name; CONTENTS column added to distinguish temporary from permanent segment allocations; SEGTYPE expanded to include LOB_DATA and LOB_INDEX
• Oracle 12c: TEMP_SPACE_ALLOCATED added to V$ACTIVE_SESSION_HISTORY allowing historical temp tracking after the session ends; V$SORT_USAGE itself unchanged
• Oracle 19c: No structural changes; Oracle Automatic In-Memory feature can reduce sort spills by promoting frequently sorted result sets to the In-Memory Column Store
• Oracle 21c / 23ai: No structural column changes; Blockchain Tables and Immutable Tables may appear as DATA segment types in multi-version query scenarios