oracle游标(oracle游标for循环)

概述

今天偷个懒，总结一下Oracle游标数的一些常用sql，下面一起来看看吧~

lect value from v$parameter where name = 'open_cursors';show parameter open_cursors;2、查看当前打开的游标数目

lect count(*) from v$open_cursor;3、查看游标使用情况

lect o.sid, osur, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs from v$open_cursor o, v$ssion s where ur_name = 'GLOGOWNER' and o.sid = s.sid group by o.sid, osur, machine,o.sql_id,o.sql_text,o.cursor_type order by num_curs desc;4、修改Oracle最大游标数

根据游标占用情况分析访问数据库的程序在资源释放上是否正常,如果程序释放资源没有问题，则加大游标数。

alter system t open_cursors=2000 scope=both;5、各用户的打开游标总数

SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME; 6、查找数据库各用户各个终端的缓存游标数

SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC# AND S.SID = A.SID AND B.NAME = 'ssion cursor cache count') AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;7、查找数据库各用户各个终端的打开游标数

SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME 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') AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！