oracle游标的查询数据库,[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;