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


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: