Temp tablespace usage

select a.tablespace_name tablespace,

         d.TEMP_TOTAL_MB,

         sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,

         d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB

from v$sort_segment a,

        (

          select   b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB

          from     v$tablespace b, v$tempfile c

          where    b.ts#= c.ts#

          group by b.name, c.block_size

        ) d

where    a.tablespace_name = d.name

group by a.tablespace_name, d.TEMP_TOTAL_MB;

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;