Monitor and Manage Oracle Temporary Tablespace

#creating New Temporary Tablespace

SQL> create temporary tablespace temptbs tempfile '+NSP' size 200m;

Tablespace created

Assign Newly Created Temporary Tablespace to Database as follows

SQL> alter database default temporary tablespace temptbs;

Drop Old tablespace Temporary tablespace on database #
Use following Queries to monitor the size of temporary tablespace 

Adding additional space to temporary tablespace on database # 

SQL> alter tablespace temptbs add tempfile '+NSP' size 100m;

Checking usage of temporary tablespace  

 select  b.Total_MB,  

   b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,  

   round(used_blocks*8/1024)        Current_Used_MB,  

   round(max_used_blocks*8/1024)       Max_used_MB  

 from  v$sort_segment a,  

   (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files )b;

Top Sessions with largest Temp Usage-

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;

 Users / SIDs / which are performing sort Operations  

 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;  

User/ Sql  Using Temp Segments  

 SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text  

 FROM v$session a, v$tempseg_usage b, v$sqlarea c  

 WHERE a.saddr = b.session_addr  

 AND c.address= a.sql_address  

 AND c.hash_value = a.sql_hash_value  

 ORDER BY b.tablespace, b.blocks;  

--Nikhil Tatineni--

--Oracle Database --