How To Find Current Open Cursors in Oracle

dbtut May 31, 2019 ORACLE

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;

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To Find Current Open Cursors in Oracle

About dbtut

Leave a Reply Cancel reply