[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;