HOW TO FIND WHO AND WHAT SQL IS USING TEMP TABLESPACE?

 
REQUEST

 
SOLUTION

CREATE OR REPLACE VIEW TEMP_USAGE AS
------------------------------------------------------------
-- Victor Khalturin
-- Version 13:25 15.07.2020 (002)
--
-- The TEMP_USAGE 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
------------------------------------------------------------
WITH TEMP_TABLESPACE_SIZE AS (
SELECT TABLESPACE_NAME,
       SUM(BYTES) MAX_BYTES
FROM
       DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME
)
SELECT NVL(S.USERNAME, 'BACKGROUND_PROCESS') USERNAME,
       S.SID,
       S.SERIAL#,
       P.SPID PID,
       S.STATUS,
       S.SQL_ID,
       S.LAST_CALL_ET ELAPSED_SEC,
       S.EVENT,
       S.MACHINE CONNNECTED_FROM,
       TU.TABLESPACE,
       TU.SEGTYPE,
       ROUND(TU.BLOCKS*TS.BLOCK_SIZE/1024/1024/1024,1) TEMP_USED_GB,
       ROUND(TU.BLOCKS*TS.BLOCK_SIZE/TTS.MAX_BYTES*100,1) PERCENT_USED
FROM
       V$SESSION S,
       V$PROCESS P,
       V$TEMPSEG_USAGE TU,
       DBA_TABLESPACES TS,
       TEMP_TABLESPACE_SIZE TTS
WHERE
       S.PADDR=P.ADDR AND
       S.SADDR=TU.SESSION_ADDR AND
       TU.TABLESPACE=TS.TABLESPACE_NAME AND TS.CONTENTS='TEMPORARY' AND
       TU.TABLESPACE=TTS.TABLESPACE_NAME;

COMMENT ON TABLE TEMP_USAGE IS '------------------------------------------------------------
-- Victor Khalturin
-- Version 13:25 15.07.2020 (002)
--
-- The TEMP_USAGE 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
------------------------------------------------------------'

------------------------------------------------------------
-- Victor Khalturin
-- Version 13:25 15.07.2020 (002)
------------------------------------------------------------

Query the view

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 TEMP_USAGE 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

Here the session 2907 with sql_id dx4nqvbtu06bx has used 95.3GB (74.5%) of all avalable space in the TEMP02 tablespace.

if you need to know a sql text of the dx4nqvbtu06bx you should query the v$sql view

SET PAGES 999
SET LINES 300
SET LONG 20000000
COL SQL_ID FOR A13
COL SQL_FULLTEXT FOR A100 
SELECT SQL_ID, SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='dx4nqvbtu06bx';

SQL_ID        SQL_FULLTEXT
------------- ----------------------------------------------------------------------------------------------------
dx4nqvbtu06bx SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND 
              SESSION_STAMP=:B1 AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1