各用戶的打開游標總數
SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;?

查找數據庫各用戶各個終端的緩存游標數
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;

查找數據庫各用戶各個終端的打開游標數
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;

?

查看游標使用情況
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 like 'BIM%' 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;

?

?

查看當前打開的游標數目
select count(*) from v$open_cursor; 
查看緩存游標數目
show parameter session_cached_cursorSELECT 'session_cached_cursors' PARAMETER,LPAD(VALUE, 5) VALUE,DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGEFROM (SELECT MAX(S.VALUE) USEDFROM V$STATNAME N, V$SESSTAT SWHERE N.NAME = 'session cursor cache count'AND S.STATISTIC# = N.STATISTIC#),(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')UNION ALLSELECT 'open_cursors',LPAD(VALUE, 5),TO_CHAR(100 * USED / VALUE, '990') || '%'FROM (SELECT MAX(SUM(S.VALUE)) USEDFROM V$STATNAME N, V$SESSTAT SWHERE N.NAME IN('opened cursors current', 'session cursor cache count')AND S.STATISTIC# = N.STATISTIC#GROUP BY S.SID),(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');

SQL>  SELECT 'session_cached_cursors' PARAMETER,LPAD(VALUE, 5) VALUE,DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGEFROM (SELECT MAX(S.VALUE) USEDFROM V$STATNAME N, V$SESSTAT SWHERE N.NAME = 'session cursor cache count'AND S.STATISTIC# = N.STATISTIC#),(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')UNION ALLSELECT 'open_cursors',LPAD(VALUE, 5),TO_CHAR  2    3    4    5    6    7    8    9   10   11   12  (100 * USED / VALUE, '990') || '%'FROM (SELECT MAX(SUM(S.VALUE)) USEDFROM V$STATNAME N, V$SESSTAT SWHERE N.NAME IN('opened cursors current', 'session cursor cache count')AND S.STATISTIC# = N.STA 13   14   15   16   17  TISTIC#GROUP BY S.SID),(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors'); 18   19  PARAMETER	       VALUE		    USAGE
---------------------- -------------------- -----
session_cached_cursors	  50		     100%
open_cursors		 300		      18%SQL> 

 查詢游標使用排名select SID,count(*) from v$open_cursor O WHERE O.user_name like 'BIM%' GROUP BY O.SID
ORDER BY 2 DESC;
----查詢游標使用情況以及游標最大數----
SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR
FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P
WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME = 'opened cursors current'
AND P.NAME = 'open_cursors'
GROUP BY P.VALUE;

?

?

?