概述
今天偷个懒,总结一下Oracle游标数的一些常用sql,下面一起来看看吧~
1、查看系统游标数(最大游标数)
select value from v$parameter where name = 'open_cursors';show parameter open_cursors;
![20153eba18326913ace3bd3f08729123.png](https://i-blog.csdnimg.cn/blog_migrate/2a29e4533685a9620310ee80cfc0d0d0.jpeg)
2、查看当前打开的游标数目
select count(*) from v$open_cursor;
![d3a9593102f68ef5e9e32ae8acd3e556.png](https://i-blog.csdnimg.cn/blog_migrate/22c44cc4476bc6823160a738caa1e6db.jpeg)
3、查看游标使用情况
select o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'GLOGOWNER' and o.sid = s.sid group by o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type order by num_curs desc;
![2ca9e20d871cff72195f743c3b6245f1.png](https://i-blog.csdnimg.cn/blog_migrate/cf7516b73522afb76767573bed90cc4f.jpeg)
4、修改Oracle最大游标数
根据游标占用情况分析访问数据库的程序在资源释放上是否正常,如果程序释放资源没有问题,则加大游标数。
alter system set open_cursors=2000 scope=both;
![157343fe41325c9b924fab3c88c47e13.png](https://i-blog.csdnimg.cn/blog_migrate/3dbc4a898469cd56bbb42339ac5f6b98.jpeg)
5、各用户的打开游标总数
SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;
![77d6fa4dcff8f4e4604f01794949e02f.png](https://i-blog.csdnimg.cn/blog_migrate/897b3a1959ba7e7dae87c1378a8c5daa.jpeg)
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 = 'session cursor cache count') AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;
![9e8eb9e0fa3a7dff0c57886c629008ea.png](https://i-blog.csdnimg.cn/blog_migrate/879bd9aeb4d54db6870b2e59175913ba.jpeg)
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;
![9263f0109f561596d4c539c63214e9ea.png](https://i-blog.csdnimg.cn/blog_migrate/96d96468538767cf07e86100d112a64f.jpeg)
后面会分享更多devops和DBA方面内容,感兴趣的朋友可以关注下!
![88c415c40d16ae0e7977add4bc8e92c0.png](https://i-blog.csdnimg.cn/blog_migrate/31d8e2315556aed223cc9817dcace3aa.jpeg)