Cacher is the code snippet organizer for pro developers

SELECT *
FROM
  (SELECT Rank() over ( ORDER BY SUM(NVL(temp_space_delta, 0)) DESC) position,
    sql_id,
    sql_plan_hash_value,
    sql_plan_operation,
    sql_plan_line_id,
    COUNT(DISTINCT sql_exec_id) total_execs,
    TRUNC(SUM(NVL(temp_space_delta, 0)) / 1024 / 1024)
    ||'m' temp_usage
  FROM
    (SELECT sql_exec_id,
      sql_id,
      sql_plan_hash_value,
      sql_plan_operation,
      sql_plan_line_id,
      temp_space_allocated - NVL(Lag(temp_space_allocated, 1) over ( PARTITION BY sql_exec_id, sql_id, session_id ORDER BY sample_id), 0) temp_space_delta
    FROM nagios.temp_segment_history
    )
  GROUP BY sql_id,
    sql_plan_operation,
    sql_plan_hash_value,
    sql_plan_line_id
  )
WHERE position <= 10
ORDER BY position;

CREATE TABLE nagios.temp_segment_history AS
SELECT *
FROM dba_hist_active_sess_history
WHERE dbid =
  (SELECT dbid FROM v$database
  )
AND snap_id IN
  (SELECT snap_id
  FROM dba_hist_snapshot
  WHERE dbid =
    (SELECT dbid FROM v$database
    )
  AND begin_interval_time BETWEEN To_date( '2015-02-27 08:00', 'yyyy-mm-dd hh24:mi') AND To_date( '2015-02-27 09:00', 'yyyy-mm-dd hh24:mi')
  );

SELECT sql_text FROM dba_hist_sqltext WHERE sql_id = '&sqlid';