如何在 Oracle 中查找当前打开的游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1010898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find Current open Cursors in Oracle
提问by M.N
What is the query to find the no. of current open cursors in an Oracle Instance?
查找编号的查询是什么？Oracle 实例中当前打开的游标数量？
Also, what is the accuracy/update frequency of this data?
另外，此数据的准确性/更新频率是多少？
I am using Oracle 10gR2
我正在使用 Oracle 10gR2
回答by Jeffrey Kemp
Total cursors open, by session:
按会话打开的游标总数：
select a.value, s.username, s.sid, s.serial#
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';
Source: http://www.orafaq.com/node/758
来源：http: //www.orafaq.com/node/758
As far as I know queries on v$ views are based on pseudo-tables ("x$" tables) that point directly to the relevant portions of the SGA, so you can't get more accurate than that; however this also means that it is point-in-time (i.e. dirty read).
据我所知，对 v$ 视图的查询基于直接指向 SGA 相关部分的伪表（“x$”表），因此您无法获得比这更准确的信息；然而这也意味着它是时间点（即脏读）。
回答by Mark Kluepfel
select sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;
appears to work for me.
似乎对我有用。
回答by WW.
Here's how to find open cursors that have been parsed. You need to be logged in as a user with access to v$open_cursor and v$session.
以下是如何查找已解析的打开游标。您需要以有权访问 v$open_cursor 和 v$session 的用户身份登录。
COLUMN USER_NAME FORMAT A15
SELECT s.machine, oc.user_name, oc.sql_text, count(1)
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY user_name, sql_text, machine
HAVING COUNT(1) > 2
ORDER BY count(1) DESC
;
If gives you part of the SQL text so it can be useful for identifying leaky applications. If a cursor has not been parsed, then it does not appear here. Note that Oralce will sometimes keep things open longer than you do.
如果为您提供部分 SQL 文本，则它可用于识别泄漏的应用程序。如果一个游标没有被解析，那么它就不会出现在这里。请注意，Oralce 有时会比您保持打开状态的时间更长。
回答by praveen s
1)your id should have sys dba access 2)
1) 您的 ID 应该具有 sys dba 访问权限 2)
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'
group by s.username, s.machine
order by 1 desc;
回答by Rana Ian
Oracle has a page for this issue with SQL and trouble shooting suggestions.
Oracle 有一个针对此问题的页面，其中包含 SQL 和故障排除建议。
"Troubleshooting Open Cursor Issues" http://docs.oracle.com/cd/E40329_01/admin.1112/e27149/cursor.htm#OMADM5352
“对打开的光标问题进行故障排除” http://docs.oracle.com/cd/E40329_01/admin.1112/e27149/cursor.htm#OMADM5352
回答by John
I use something like this:
我使用这样的东西：
select
user_name,
count(*) as "OPEN CURSORS"
from
v$open_cursor
group by
user_name;
