[DB][ORACLE]数据库活动游标数查询SQL

数据库活动游标数查询SQL

SELECT ALLCURSOR 所有游标,
       MYALLCURSOR 我的游标数,
       MYOPENCURSOR 我打开游标数,
       MYCACHECURSOR 我SESSION缓存游标数,
       (MYALLCURSOR - MYOPENCURSOR - MYCACHECURSOR) 我的其他游标数
  FROM (SELECT (SELECT COUNT(*) FROM V$OPEN_CURSOR A) ALLCURSOR,
               (SELECT COUNT(*)
                  FROM V$OPEN_CURSOR A
                 WHERE A.USER_NAME = 'TELSOFT_R_01') MYALLCURSOR,
               (SELECT SUM(AA.VALUE)
                  FROM (SELECT A.VALUE
                          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'
                           AND S.USERNAME = 'TELSOFT_R_01') AA) MYCACHECURSOR,
               (SELECT SUM(AA.VALUE)
                  FROM (SELECT A.VALUE
                          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'
                           AND S.USERNAME = 'TELSOFT_R_01') AA) MYOPENCURSOR
          FROM DUAL);
          
--查找各用户的打开游标总数（含缓存游标）
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;