Temp Tablespace Usage

Script to give an idea about the usage of temp tablespace -

SELECT sql_text,
sum(onepass_executions) onepass_cnt,
sum(multipasses_executions) mpass_cnt
FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(onepass_executions+multipasses_executions)>0;

Script to show allocation on the temp tablespace -

SELECT TO_NUMBER(DECODE(sid, 65535, NULL, sid)) sid,
operation_type OPERATION,
TRUNC(expected_size/1024) ESIZE,
TRUNC(actual_mem_used/1024) MEM,
TRUNC(max_mem_used/1024) MAXMEM,
number_passes PASS,
TRUNC(tempseg_size/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

Following script you can monitor the actual space used in a temporary tablespace and see the HWM of the temporary tablespace. The script is designed to run when there is only one temporary tablespace in the database.

select sum( u.blocks * blk.block_size)/1024/1024 "Mb. in sort segments",
(hwm.max * blk.block_size)/1024/1024 "Mb. High Water Mark"
from v$sort_usage u,
(select block_sizefrom dba_tablespaceswhere contents = 'TEMPORARY') blk,
(select segblk#+blocks maxfrom v$sort_usagewhere segblk# = (select max(segblk#)
from v$sort_usage) ) hwm
group by hwm.max * blk.block_size/1024/1024;