***********************************************************************
* Description: SQL for querying temp tablespace utilization by session
* Compatiablity: RDBMS 11g, 12c
* Date: 04:08 PM EST, 04/01/2017
***********************************************************************


<1> Checking temp tablespace usage by session: -- V$SORT_USAGE can be replacable by view V$TEMPSEG_USAGE
     |
     |__ SQL> select 
                 srt.tablespace, 
                 srt.segfile#, 
                 srt.segblk#, 
                 srt.blocks, 
                 a.sid, 
                 a.serial#, 
                 a.username, 
                 a.osuser, 
                 a.status 
              from 
                 v$session a,
                 v$sort_usage srt 
              where 
                 a.saddr = srt.session_addr 
              order by 
                 srt.tablespace, srt.segfile#, srt.segblk#, 
                 srt.blocks;	 
				 
				 
				 
				 
<2> Similiar query with more detail:
     |
     |__ SQL> SELECT   b.TABLESPACE
                     , a.paddr
                     , a.saddr
                     , 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;
              	   
              	   
              TABLESPACE        SEGFILE#    SEGBLK#    SIZE_MB        SID    SERIAL# USERNAME               OSUSER                         PROGRAM
              --------------- ---------- ---------- ---------- ---------- ---------- --------------------- ------------------------------ -------------
              TEMP                   201     271232          1        364      20645 SYSTEM                 NT AUTHORITY\ANONYMOUS LOGON   w3wp.exe
              TEMP                   201     272640          1        364      20645 SYSTEM                 NT AUTHORITY\ANONYMOUS LOGON   w3wp.exe
              TEMP_RPT               202     127104         16        359      60693 RPTVIEW                NT AUTHORITY\ANONYMOUS LOGON   CrystalService.exe
              TEMP_RPT               202     129152         16        359      60693 RPTVIEW                NT AUTHORITY\ANONYMOUS LOGON   CrystalService.exe
              TEMP_RPT               202     387200         16        359      60693 RPTVIEW                NT AUTHORITY\ANONYMOUS LOGON   CrystalService.exe
              TEMP_RPT               202     389248         16        359      60693 RPTVIEW                NT AUTHORITY\ANONYMOUS LOGON   CrystalService.exe
              TEMP_RPT               203      36992         64        359      60693 RPTVIEW                NT AUTHORITY\ANONYMOUS LOGON   CrystalService.exe
              TEMP_RPT               203    1138816         16        359      60693 RPTVIEW                NT AUTHORITY\ANONYMOUS LOGON   CrystalService.exe