How To Find Current Open Cursors in Oracle

dbtut

6 years ago

In Oracle, you can find the open cursors with the following query. The following error occurs when the number of open cursors reaches the open_cursors parameter.

ORA-01000: maximum open cursors exceeded

The open_cursors parameter can be increased to prevent errors.

The following query can be used to find open cursors.

SQL> SELECT A.VALUE,
       S.USERNAME,
       S.SID,
       S.SERIAL#,
       S.SQL_ID
  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'
 ORDER BY 1 DESC;

SQL> SELECT A.VALUE,

S.USERNAME,

S.SID,

S.SERIAL#,

S.SQL_ID

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'

ORDER BY 1 DESC;