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:

WordPress.com Logo

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

Google photo

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

Twitter picture

You are commenting using your Twitter 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 WordPress.com.

%d bloggers like this: