Some Scripts for TEMP Tablespace usage

Temp Tablespace Usage

set linesize 1000
col tablespace format a10 trunc
col username format a8 trunc
col osuser format a6 trunc
col sid_pid format a10 trunc
col machine format a10 trunc
col sid format 9990
col serial# format 99990
col spid format a5 trunc
compute sum label 'TOTAL' of blocks_mb on report;
break on report

select t1.tablespace
, sum( t1.blocks * ( select to_number(value) from v$parameter where name = 'db_block_size') ) / 1024 / 1024 blocks_mb , sum( t1.extents ) tot_extents
, t2.username , t2.osuser, t2.status, t2.SID ',' t2.serial# SID_PID
, t4.spid, t2.sql_address, t1.sqlhash, t1.sql_idfrom v$sort_usage t1
, v$session t2, v$process t4
where t1.SESSION_ADDR = t2.SADDR
and (t2.PROCESS=t4.SPID or t2.paddr = t4.addr)
group by t1.tablespace , t2.username , t2.osuser , t2.machine , t2.status
, t2.schemaname , t2.program , t2.SID ',' t2.serial# , t4.spid
, t2.sql_address , t1.sqlhash , t1.sql_id order by 2;


This will give the size of the temporary tablespace:

select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;

This will give the "high water mark" of that temporary tablespace (= max used at one time):

select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;

This will give current usage:

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;