Oracle Temp Tablespace sql

select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text

  from v$sort_usage sort, v$session sess, v$sql sql

 where sort.SESSION_ADDR = sess.SADDR

   and sql.ADDRESS = sess.SQL_ADDRESS

 order by blocks desc;

 

 

SELECT D.tablespace_name,
       SPACE "SUM_SPACE(M)",
       blocks "SUM_BLOCKS",
       used_space "USED_SPACE(M)",
       Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
       SPACE - used_space "FREE_SPACE(M)"
  FROM (SELECT tablespace_name,
               Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
               SUM(blocks) BLOCKS
          FROM dba_temp_files
         GROUP BY tablespace_name) D,
       (SELECT tablespace,
               Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
          FROM v$sort_usage
         GROUP BY tablespace) F
 WHERE D.tablespace_name = F.tablespace(+)
   AND D.tablespace_name in ('TEMP', 'TEMP2');