oracle temporary tablespace - ghdrako/doc_snipets GitHub Wiki

cat tsu.sql
set lines 120 pages 999
set head on
col tablespace for a30

SELECT tablespace, round(sum(blocks)*8/1024) MB
  FROM v$tempseg_usage
GROUP BY tablespace
/

SELECT segtype, round(sum(blocks)*8/1024) MB
  FROM v$tempseg_usage
 WHERE tablespace like upper('&&1%')
GROUP BY segtype
/

SELECT s.sid, t.segtype, round(sum(t.blocks)*8/1024) MB
  FROM v$tempseg_usage t,
       v$session s
 WHERE t.session_addr = s.saddr
   AND tablespace like upper('&&1%')
GROUP BY s.sid, t.segtype
HAVING round(sum(t.blocks)*8/1024) > nvl(to_number('&2 '),0)
/

The three queries you provided offer a progressive look at temporary‐tablespace consumption: the first aggregates usage by tablespace, the second breaks it down by segment type within a specific tablespace, and the third pinpoints sessions whose temp usage by segment type exceeds a given threshold. These scripts leverage the dynamic performance view V$TEMPSEG_USAGE (also synonym for V$SORT_USAGE) to report on blocks allocated in temporary segments, joined to V$SESSION when session‐level detail is needed.

Oracle’s V$TEMPSEG_USAGE describes temporary‐segment usage (i.e. space allocated in TEMP tablespaces for sorts, hashes, LOBs, etc.) Dokumentacja Oracle . It is functionally equivalent to V$SORT_USAGE and includes these key columns:

  • TABLESPACE – name of the TEMP tablespace Dokumentacja Oracle
  • SEGTYPE – type of temporary segment (SORT, HASH, DATA, INDEX, LOB_DATA, LOB_INDEX) Oracle Forums
  • BLOCKS – number of blocks allocated to that segment
  • SESSION_ADDR and SESSION_NUM – identifiers to join back to session details

2.1 Aggregate by Tablespace

SELECT tablespace,
       ROUND(SUM(blocks)*8/1024) MB
  FROM v$tempseg_usage
 GROUP BY tablespace;

Purpose: Shows total MB allocated per temporary tablespace.
Database Journal

Calculation: SUM(blocks)*8/1024 converts Oracle blocks (typically 8 KB) into megabytes.
Bobby Durrett's DBA Blog

When to use: Good for a quick health check to see which TEMP tablespace is under pressure.

2.2 Breakdown by Segment Type

SELECT segtype,
       ROUND(SUM(blocks)*8/1024) MB
  FROM v$tempseg_usage
 WHERE tablespace LIKE UPPER('&&1%')
 GROUP BY segtype;

Purpose: Within a given TEMP (entered as &&1), shows how much space each type of segment (e.g. SORT vs. HASH) is consuming.
Paul Stuart's Oracle Blog

Use case: Helps distinguish whether sorts, hash joins, LOB operations, etc., are driving temp usage.

2.3 Session-Level, Segment-Type Threshold

SELECT s.sid,
       t.segtype,
       ROUND(SUM(t.blocks)*8/1024) MB
  FROM v$tempseg_usage t
       JOIN v$session s ON t.session_addr = s.saddr
 WHERE tablespace LIKE UPPER('&&1%')
 GROUP BY s.sid, t.segtype
HAVING ROUND(SUM(t.blocks)*8/1024) > NVL(TO_NUMBER('&2'),0);

Purpose: Identifies sessions (SID) whose usage by SEGTYPE exceeds a user-supplied threshold (&2 MB).
Stack Overflow

Benefit: Quickly spots runaway sessions monopolizing TEMP space.

Enhancements & Best Practices

3.1 Link to SQL Text via SQL_ID

Add SQL_ID from V$TEMPSEG_USAGE (or join V$SORT_USAGE including that column) and then join to V$SQL/V$SQLTEXT to see the exact statement consuming temp. Oracle Forums

SELECT tu.session_addr,
       tu.sql_id,
       sq.sql_text,
       SUM(tu.blocks)*8192/1024 MB
  FROM v$tempseg_usage tu
  JOIN v$sql sq ON tu.sql_id = sq.sql_id
 WHERE tu.tablespace = 'TEMP'
 GROUP BY tu.session_addr, tu.sql_id, sq.sql_text;

3.3 Historical Trending with ASH/AWR

To see past high-temp usage events, use V$ACTIVE_SESSION_HISTORY’s TEMP_SPACE_ALLOCATED column or AWR’s DBA_HIST_ACTIVE_SESS_HISTORY. Bobby Durrett's DBA Blog

SELECT sample_time, session_id, temp_space_allocated/1024/1024 MB
  FROM dba_hist_active_sess_history
 WHERE temp_space_allocated > 0
 ORDER BY sample_time DESC;

3.4 Alerting & Thresholds

Incorporate these queries into automated monitoring (e.g. Oracle Enterprise Manager, custom shell/PLSQL jobs) to alert when TEMP usage exceeds, say, 80% of tablespace capacity, or when any single session uses > X MB.

4. Segment Types (SEGTYPE) Explained

Common values in V$TEMPSEG_USAGE.SEGTYPE: