Oracle 诊断临时暴涨的诊断

要想诊断过去临时表空间不足的问题。

### Cause: org.hibernate.exception.GenericJDBCException: ORA-30928: 使用过滤状态的连接用尽了临时表空间

ORA-30928: Connect by filtering phase runs out of temp tablespace
sponsored links
Cause: It is probably caused by the fact that there is a loop in the data.
Action: Please retry the query with the NO_FILTERING hint. If the same error still occurs,then increase temp tablespace.

如果具备一些知识，那就简单了：

1. 等待事件，有关于临时表空间的等待事件是direct path write temp和direct path read temp

2. Oracle会每秒记录session的状态，就是v$session的快照，v$session之中有一个event就是等待事件。

select s.sql_id,count(1) cc

from dba_hist_active_sess_history s
where s.event = 'direct path write temp'
or s.event = 'direct path read temp'
group by sql_id
order by cc desc

select s.SQL_ID,s.sql_text
from dba_hist_sqltext s
where s.sql_id in ('bn75bv0wqtk65','a3tvu1jyvm8tq','7r3pa4bjhprtn',
'bmndrgc7xkmjb','fsdpqafcaxfra')

with t as( select s.sql_id,count(1) cc from dba_hist_active_sess_history s where (s.event = 'direct path write temp' or s.event = 'direct path read temp') and s.snap_id in( select snap_id from (select snap_id from dba_hist_snapshot s order by s.snap_id desc) where rownum <= 3 ) group by sql_id) select s.SQL_ID,s.sql_text,cc from dba_hist_sqltext s,t where s.sql_id = t.sql_id order by cc desc;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!