Scripts related to TEMP Tablespace

Scripts related to TEMP Tablespace 


 To see Temp Space:
 -------------------

 SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
 "Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
 ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
 ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",            
 ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
 ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
 FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
 total_blocks FROM dba_temp_files GROUP by tablespace_name) c
 WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;


 =================================================================
 To see top 10 consuming process :
 ---------------------------------------

 select * from
 (SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
 FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
 WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND d.tablespace_name=b.tablespace ORDER BY b.tablespace, b.blocks DESC)
 where rownum <=10


 ===============================================================

 Query to check TEMP USAGE :
 ------------------------------------

 col name for a20
 SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
 "ExtManag",
 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
 0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
 FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
 tablespace_name) a,
 (select tablespace_name, sum(bytes_cached) bytes from
 v$temp_extent_pool group by tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
 AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';



 ==================================================================== 

 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 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 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;

 ====================================================================

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


 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;


 ==============================================================================================================================================