Oracle 查詢佔用臨時表空間大的歷史會話和SQL

alter 日誌中報 ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP。

可透過下面方式定為到歷史sql:

--佔用臨時表空大的歷史會話和sql查詢：

select to_char(a.sample_time, 'yyyy-mm-dd hh24'),

       a.session_id,

       u.username,

       a.sql_id

  from gv$active_session_history a, dba_users u

 where u.user_id = a.user_id

   and to_char(a.sample_time, 'yyyy-mm-dd hh24:mi:ss') >

       '2021-05-25 20:30:00'

   and to_char(a.sample_time, 'yyyy-mm-dd hh24:mi:ss') <

       '2021-05-25 20:40:59'

   and a.temp_space_allocated > 10000000

   and sql_id is not null

 group by to_char(a.sample_time, 'yyyy-mm-dd hh24'),

          a.session_id,

          u.username,

          a.sql_id

 order by a.sql_id, a.session_id desc;

或

select to_char(a.sample_time, 'yyyy-mm-dd hh24'),

       a.session_id,

       u.username,

       a.sql_id

  from dba_hist_active_sess_history a, dba_users u

 where u.user_id = a.user_id

   and to_char(a.sample_time, 'yyyy-mm-dd hh24:mi:ss') >

       '2020-07-09 11:00:00'

   and to_char(a.sample_time, 'yyyy-mm-dd hh24:mi:ss') <

       '2020-07-09 11:10:00'

   and a.temp_space_allocated > 1000000000

   and sql_id is not null

 group by to_char(a.sample_time, 'yyyy-mm-dd hh24'),

          a.session_id,

          u.username,

          a.sql_id

 order by a.sql_id, a.session_id desc;

--檢視sql佔用臨時表空間最大值：
select max(a.temp_space_allocated / 1024 / 1024 / 1024) g
  from gv$active_session_history a
 where a.sql_id = '6uk7dr0n12f9n';

--查詢當前佔用臨時表空間高的SQL

select se.username,

       se.sid,

       su.extents,

       su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as used_G,

       tablespace,

       segtype,

       s.sql_id,

       s.sql_text

  from v$sort_usage su, v$parameter p, v$session se, v$sql s

 where p.name = 'db_block_size'

   AND su.session_addr = se.saddr

   AND s.hash_value = su.sqlhash

   AND s.address = su.sqladdr

   and su.blocks > 1000

 order by su.blocks desc;