Top 10 Sessions With Largest Temp Usage

Top 10 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;