结果集耗尽时，检查是否关闭结果集时常用sql

--打开的游标统计
select sum(a.value) total_cur,
avg(a.value) avg_cur,
max(a.value) max_cur,
s.username,
s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid = a.sid
and b.name = 'opened cursors current'
and machine like '%yaohuiqin%'
group by s.username, s.machine
order by 1 desc;

--根据用户名、sid统计游标
select o.sid, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s
where user_name = 'BXCWGZ' and o.sid=s.sid
and machine like '%yaohuiqin%'
and cursor_type in ('OPEN','OPEN-RECURSIVE')
group by o.sid, osuser, machine
order by num_curs desc;

--查询指定用户下游标及对应sql
select q.sql_text,cursor_type,o.sid
from v$open_cursor o, v$sql q
where q.sql_id=o.sql_id and o.sid in('209','7','221','366','63','92','465','364','163','109','135')
and cursor_type in ('OPEN','OPEN-RECURSIVE')
and q.sql_text not like '%$%'
and q.sql_text like '%LCurrency%'
order by o.sid,q.LAST_LOAD_TIME desc

Select FCurCode From A2019297LCurrency where FIsBase=1

Select FCurCode From A2019441LCurrency where FIsBase=1

select q.sql_text,cursor_type,o.sid
from v$open_cursor o, v$sql q
where q.sql_id=o.sql_id and o.sid ='309'
and cursor_type in ('OPEN','OPEN-RECURSIVE')
and q.sql_text not like '%$%'
order by o.sid,q.LAST_LOAD_TIME desc

--同上，分组显示
select q.sql_text, count(1)
from v$open_cursor o, v$sql q
where q.sql_id=o.sql_id and o.sid = 286
group by q.sql_text
order by count(1) desc

select cursor_type,count(*) from v$open_cursor where sid = 286 group by cursor_type

select cursor_type,count(*) from v$open_cursor where sid = 286 group by cursor_type

select * from v$open_cursor where sid = 465 and cursor_type in ('OPEN','OPEN-RECURSIVE')

转载于:https://www.cnblogs.com/yaohuiqin/p/10636660.html