Oracle view temp tablespace segment tips

Question:  I want to be able to see temporary segments created and destroyed within my TEMP tablespace, and I would also like to tie-in TEMP segments with the session that has created the TEMP tables.  Are these scripts to map the segments in the TEMP tablespace?

Answer:  Oracle uses the TEMP tablespace for a variety of tasks, primarily for sorting rows, and holding the intermediate row sets between n-way tables joins, but also for temporary table row storage.

Both subquery factoring (WITH clause) and global temporary tables will write the intermediate results to the TEMP tablespace.  Oracle stores GTT rows in the users temporary tablespace of the user, but you can change this to a "real" tablespace by using the tablespace clause of the create global temporary table syntax.

You can check for held TEMP segments with this query:

select
   srt.tablespace,
   srt.segfile#,
   srt.segblk#,
   srt.blocks,
   a.sid,
   a.serial#,
   a.username,
   a.osuser,
   a.status
from
   see code depot for full scripts
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,
   srt.blocks;

The Oracle script collection has a script that will display all TEMP segments in the TEMP tablespace, but Oracle does not have any way to join v$session information into the actual objects created within the TEMP tablespace.

For sorting in the TEMP tablespace, the v$sort_usage view can be queried to see what session is storing temporary segments in the TEMP tablespace:

select
   (select
      username
   from
      v$session
   where
      saddr = session_addr) uname,
   v.*
from
   v$sort_usage v;

The v$sort_usage will show more than just disk sorts. The sort segment in v$sort_usage can be temporary tables, open cursors or some temporary LOBs. 

select
   a.name,
   b.value
from
   v$statname    a,
   v$sesstat     b,
   v$session     s,
   v$sort_usage su
where
   a.statistic#=b.statistic#
and
   b.sid=s.sid
and
   s.saddr=su.session_addr;