Dealing with Temp Tablespace in Oracle


------- Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;


SELECT   b.TABLESPACE, b.segfile# , b.segblk# , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb , a.SID , a.serial# , a.username , a.osuser , a.program , a.status FROM v$session a , v$sort_usage b, v$process c, v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr ORDER BY b.TABLESPACE, b.segfile# , b.segblk#, b.blocks;



----The query below will display which sessions are using TEMP tablespace and how much space is being used by each session:

SELECT   b.TABLESPACE, b.segfile#, b.segblk# , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb, a.SID, a.serial#, a.username, a.osuser, a.program, a.status  FROM v$session a , v$sort_usage b , v$process c , v$parameter p   WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr  AND a.paddr = c.addr ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;


-----To see how much space is being used and free in TEMP tablespace run the following sql

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM   v$temp_space_header GROUP  BY tablespace_name;

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;


---- To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;



---- To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block" from v$sort_usage group by username;



----To find Sort Segment Usage by a particular User

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr order by u.blocks desc;



----- To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,
(used_blocks*8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%';


-----------Get 10 sessions with largest temp usage

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;