查看oracle是否open,Oracle查看已被使用的open_cursors&session_cached_cursors

查看当前session已使用的最大open cursor数 和cached cursor数：SELECT 'session_cached_cursors' PARAMETER,

LPAD (VALUE, 5) VALUE,

DECODE (VALUE, 0, ' n/a', TO_CHAR (100 * USED / VALUE, '990') || '%')

USAGE

FROM (SELECT MAX (S.VALUE) USED

FROM V$STATNAME N, V$SESSTAT S

WHERE     N.NAME = 'session cursor cache count'

AND S.STATISTIC# = N.STATISTIC#),

(SELECT VALUE

FROM V$PARAMETER

WHERE NAME = 'session_cached_cursors')

UNION ALL

SELECT 'open_cursors',

LPAD (VALUE, 5),

TO_CHAR (100 * USED / VALUE, '990') || '%'

FROM (  SELECT MAX (SUM (S.VALUE)) USED

FROM V$STATNAME N, V$SESSTAT S

WHERE     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');

注：如果查询的session open cursor到达100% client就很可能报错ORA-01000

查看session open cursor的具体程式、open cursor数量：

SELECT a.inst_id,

a.sid,

a.USERNAME,

a.SCHEMANAME,

a.OSUSER,

a.machine,

a.TERMINAL,

a.LOGON_TIME,

a.PROGRAM,

a.STATUS,

b.name,

b.used

FROM gv$session a,

(SELECT n.inst_id,

sid,

n.name,

s.VALUE used

FROM gv$statname n, gv$sesstat s

WHERE     n.name IN ('opened cursors current',

'session cursor cache count')

AND s.statistic# = n.statistic#

AND n.inst_id = s.inst_id) b

WHERE     a.sid = b.sid

AND a.inst_id = b.inst_id

AND b.name <> 'session cursor cache count'

ORDER BY b.used DESC;

查看session open cursor的具体SQL(此方法是联合v$open_cursor得出的结果，可能有不准确。但session最多open_cursors的SQL一定是在运行结果中)

SELECT distinct a.inst_id,

a.sid,

a.USERNAME,

a.SCHEMANAME,

a.OSUSER,

a.machine,

a.TERMINAL,

a.LOGON_TIME,

a.PROGRAM,

a.STATUS,

b.name,

b.used,c.sql_id

FROM   gv$session a,

(SELECT   n.inst_id, sid, n.name, s.VALUE used

FROM   gv$statname n, gv$sesstat s

WHERE   n.name IN

('opened cursors current',

'session cursor cache count')

AND s.statistic# = n.statistic# and n.inst_id=s.inst_id ) b,v$open_cursor c

WHERE   a.sid = b.sid and a.inst_id = b.inst_id and a.sid=c.sid and c.CURSOR_TYPE in('OPEN','OPEN-PL/SQL','OPEN-RECURSIVE')

and b.name <> 'session cursor cache count'

order by b.used desc;