temp_usage.sql

--------------------------------------------------------------------------------
-- Script:        temp_usage.sql
-- Purpose:       Report temporary tablespace allocation and current usage, plus the
--                sessions/SQL consuming the most temp space, to diagnose ORA-01652
--                ("unable to extend temp segment") and runaway sort/hash operations.
-- Compatibility: Oracle 19c, 21c (Enterprise Edition). RAC-aware via GV$ views.
-- Privileges:    SELECT on DBA_TEMP_FILES, V$TEMP_SPACE_HEADER, GV$SORT_USAGE,
--                GV$SESSION, GV$SQL (or SELECT_CATALOG_ROLE).
--
-- Example execution:
--   SQL> @temp_usage.sql
--
-- Explanation:
--   * The first section sizes each TEMP tablespace and shows how much is currently
--     in use vs free. Sustained high usage hints at undersized TEMP or bad plans.
--   * The second section attributes temp usage to live sessions and the exact SQL,
--     so a DBA can identify the offending query/user rather than just "temp is full."
--   * Temp space is released when the operation finishes; a single large sort can
--     temporarily spike usage without indicating a permanent problem.
--
-- Sanitization:  No hostnames, IPs, SIDs, credentials, or company data.
--------------------------------------------------------------------------------

SET LINESIZE 200
SET PAGESIZE 100
SET FEEDBACK OFF
COLUMN tablespace_name FORMAT A22
COLUMN total_gb        FORMAT 999,990.00
COLUMN used_gb         FORMAT 999,990.00
COLUMN free_gb         FORMAT 999,990.00
COLUMN pct_used        FORMAT 990.0
COLUMN username        FORMAT A18
COLUMN sid_serial      FORMAT A14
COLUMN temp_mb         FORMAT 999,990
COLUMN sql_id          FORMAT A14

PROMPT
PROMPT ======================= TEMP TABLESPACE USAGE ==========================
SELECT th.tablespace_name,
       ROUND(SUM(th.bytes_used + th.bytes_free) / 1024/1024/1024, 2) AS total_gb,
       ROUND(SUM(th.bytes_used)                 / 1024/1024/1024, 2) AS used_gb,
       ROUND(SUM(th.bytes_free)                 / 1024/1024/1024, 2) AS free_gb,
       ROUND(SUM(th.bytes_used) * 100 /
             NULLIF(SUM(th.bytes_used + th.bytes_free), 0), 1)       AS pct_used
FROM   v$temp_space_header th
GROUP  BY th.tablespace_name
ORDER  BY pct_used DESC;

PROMPT
PROMPT =================== TOP TEMP CONSUMERS (LIVE SESSIONS) ==================
SELECT s.username,
       s.sid || ',' || s.serial#                         AS sid_serial,
       s.inst_id,
       ROUND(su.blocks * dt.block_size / 1024/1024)       AS temp_mb,
       su.tablespace                                       AS temp_ts,
       su.sql_id
FROM   gv$sort_usage su
JOIN   gv$session    s  ON s.saddr = su.session_addr
                        AND s.inst_id = su.inst_id
JOIN   dba_tablespaces dt ON dt.tablespace_name = su.tablespace
ORDER  BY su.blocks DESC
FETCH FIRST 15 ROWS ONLY;

PROMPT
SET FEEDBACK ON