CREATE OR REPLACE VIEW DBT_TEMPUSAGE AS
------------------------------------------------------------
-- 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
------------------------------------------------------------
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 DBT_TEMPUSAGE IS '------------------------------------------------------------
-- 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
------------------------------------------------------------'

------------------------------------------------------------
-- Victor Khalturin
-- Version 13:27 07-Jan-2022 (001)
------------------------------------------------------------