A query to know which sessions are currently using temporary space in the Oracle Database, and how much space each session consumes.
GOAL

A query to find out user sessions that are using temporary tablespace.

SOLUTION

With the DBT_TEMPUSAGE view, you can point out which user, or session, or SQL is currently using the temporary tablespace.

An example of the information provided by the view

------------------------------------------------------------
-- Victor Khalturin
-- Version 13:27 07-Jan-2022 (001)
--
-- The DBT_TEMPUSAGE displays current information about a Temporary Tablespace usage
--
-- USERNAME        - User who requested temporary space
-- SID             - Session identifier
-- SERIAL#         - Session serial number
-- PID             - Operating system process identifier
-- STATUS          - Status of the session
-- ELAPSED_SEC     - Elapsed time in seconds since the session has changed its status
-- EVENT           - Resource or event for which the session is waiting
-- CONNNECTED_FROM - Operating system machine name the connection is established from
-- TABLESPACE      - Tablespace in which space is allocated
-- SEGTYPE         - Type of sort segment
-- TEMP_USED_GB    - Space in GB allocated to the sort
-- PERCENT_USED    - Percent(%) used for a tablespace
------------------------------------------------------------

SET PAGES 999
SET LINES 300
COL USERNAME FOR A10
COL SID FOR 99999
COL PID FOR A5
COL STATUS FOR A8
COL SQL_ID FOR A13
COL EVENT FOR A30
COL CONNNECTED_FROM FOR A22
COL TABLESPACE FOR A10
COL SEGTYPE FOR A10

SELECT * FROM DBT_TEMPUSAGE WHERE STATUS='ACTIVE' AND TEMP_USED_GB>0;
USERNAME      SID SERIAL# PID   STATUS   SQL_ID        ELA  EVENT                        CONNNECTED_FROM TABLESPACE SEGTYPE TEMP_USED_GB PERCENT_USED
---------- ------ ------- ----- -------- ------------- ---- ---------------------------- --------------- ---------- ------- ------------ ------------
DBSNMP       2907   56461 1191  ACTIVE   dx4nqvbtu06bx  551 direct path write temp       s001db-ecm01    TEMP02     HASH            95.3         74.5
DBSNMP       3473   29755 20925 ACTIVE   5qqs87rdfucuu  552 control file sequential read s001db-ecm01    TEMP02     HASH             8.9          6.9

Written At
07 JAN 202214:00
Red Hat Release
7.x x64
Database Release
11.2.0.4 - 19.13.0.0.0