概述

今天偷個懶，總結一下Oracle游標數的一些常用sql，下面一起來看看吧~

1、查看系統游標數(最大游標數)

select value from v$parameter where name = 'open_cursors';
show parameter open_cursors;

2、查看當前打開的游標數目

select count(*) from v$open_cursor;

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;

4、修改Oracle最大游標數

根據游標占用情況分析訪問資料庫的程序在資源釋放上是否正常,如果程序釋放資源沒有問題，則加大游標數。

alter system set 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 = 'session 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方面內容，感興趣的朋友可以關注下！