Show Temporary Space Usage

Need to know how much temp space is in use? By which session/SQL command?

Show the current temporary space usage:

-- current temp space usage
select tablespace
,      contents
,      segtype
,      used_mb
,      round(used_mb/1024) used_gb
from   (
  select tsu.tablespace
  ,      tsu.contents
  ,      tsu.segtype
  ,      round((tsu.blocks * tsp.block_size / 1024 / 1024)) used_mb
  from   v$tempseg_usage tsu
    inner join dba_tablespaces tsp
            on tsu.tablespace = tsp.tablespace_name
    )
;

Show the current temporary space usage per tablespace:

-- current temp space usage per tablespace
select tablespace
,      size_mb
,      used_mb
,      size_mb - used_mb as free_mb
,      round(size_mb/1024) size_gb
,      round(used_mb/1024) used_gb
,      round(size_mb/1024) - round(used_mb/1024) as free_gb
,      round(nvl((size_mb - used_mb)/size_mb * 100, 0)) pct_free
from   (
  select tsu.tablespace
  ,      df.size_mb
  ,      round((tsu.blocks * tsp.block_size / 1024 / 1024)) used_mb
  from   (
            select tablespace
            ,      sum(blocks) blocks
            from   v$tempseg_usage
            group by tablespace
         ) tsu
    inner join (
                  select tablespace_name
                  ,      round(sum(bytes)/1014/124) size_mb 
                  from   dba_temp_files
                  group by tablespace_name
                  union all
                  select tablespace_name
                  ,      round(sum(bytes)/1014/124) size_mb 
                  from   dba_data_files
                  group by tablespace_name
               ) df
            on tsu.tablespace = df.tablespace_name
    inner join dba_tablespaces tsp
            on tsu.tablespace = tsp.tablespace_name
    )
;

Show the current temporary space usage per session:

-- temp space usage per session
select ses.sid
,      ses.serial#
,      ses.username
,      ses.osuser
,      ses.process
,      ses.machine
,      ses.module
,      ses.program
,      tsu.tablespace
,      tsu.contents
,      tsu.segtype
,      round(tsu.blocks * tsp.block_size / 1024 / 1024) as used_mb
,      round(tsu.blocks * tsp.block_size / 1024 / 1024 / 1024) as used_gb
,      sql.sql_fulltext
from   v$tempseg_usage tsu
  inner join v$session ses
          on tsu.session_addr = ses.saddr
  inner join dba_tablespaces tsp
          on tsu.tablespace = tsp.tablespace_name
  left outer join v$sql sql
          on tsu.sql_id = sql.sql_id
;