Temporary usage per session
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
Temporary space in use currently
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_size from dba_tablespaces where contents = ‘TEMPORARY’) blk , (select segblk#+blocks max from v$sort_usage where segblk# = (select max(segblk#) from v$sort_usage) ) hwm group by hwm.max * blk.block_size/1024/1024;
Max space used
select tablespace_name,current_users,round (USED_BLOCKS*8/1024/1024,2) “GB in Use”,
round(FREE_BLOCKS*8/1024/1024,2) “Free GB”,round(MAX_SIZE/1024,2) “Msize GB”,
round(MAX_USED_BLOCKS*8/1024/1024,2) “Max used GB”
from v$sort_segment;
PS. Need update the source
Oracle Logbook 