Oracle: Analyzing Tempspace usage

--Who and What are taking up all the temp space ?

select s.inst_id, s.username, s.sid, s.serial#, p.spid, u.tablespace, u.extents, u.blocks
from gv$session s, gv$sort_usage u, gv$process p where u.session_addr=s.saddr and
s.paddr = p.addr and u.contents='TEMPORARY' and u.extents !=1;

--Overall information with tempspace

set linesize 1000 pages 120

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

select * from dba_temp_files;

select inst_id, extent_size*total_extents*(select value from v$parameter where name = 'db_block_size')/1024/1024 as Tempspace_Used from gv$sort_segment;

select tablespace_name, current_users, total_extents, used_extents, max_size from v$sort_segment; 

select s.inst_id, s.username, s.sid, s.serial#, u.tablespace, u.extents, u.blocks from gv$session s, gv$sort_usage u where u.session_addr=s.saddr and u.contents='TEMPORARY';

select inst_id, username , sum(blocks)*8192/1024/1024 MB_used from gv$sort_usage bytes_used group by inst_id , username order by 1;

select FREE_EXTENTS, USED_EXTENTS , FREED_EXTENTS, MAX_SIZE, MAX_USED_SIZE from gv$sort_segment;

select * from gV$TEMPSEG_USAGE;

select sql_id from gV$TEMPSEG_USAGE;