Oracle Logbook

July 22, 2011

Temporary Tablespace usage

Filed under: Uncategorized — Helio Dias @ 8:11 pm

 

 

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

Blog at WordPress.com.