garbagedump

dba | ORA-01000: maximum open cursors exceeded

weathered's photo
weathered
·

1 min read

not the best place for explanations here, the below query shows the number of open cursors present in a session. if the number reaches the db parameterOPEN_CURSORS, ORA-01000 is thrown.

OPEN_CURSORS := number of cursors each session can have open, per session

SELECT STAT.VALUE,
       SESS.USERNAME,
       SESS.SID,
       SESS.SERIAL#
  FROM V$SESSTAT STAT, V$STATNAME B, V$SESSION SESS
 WHERE     STAT.STATISTIC# = B.STATISTIC#
       AND SESS.SID = STAT.SID
       AND B.NAME = 'opened cursors current';

a summary report by username and machine, helpful if you are on the other end:

  SELECT SUM (STAT.VALUE)     TOTAL_CUR,
         AVG (STAT.VALUE)     AVG_CUR,
         MAX (STAT.VALUE)     MAX_CUR,
         SESS.USERNAME,
         SESS.MACHINE
    FROM V$SESSTAT STAT, V$STATNAME B, V$SESSION SESS
   WHERE     STAT.STATISTIC# = B.STATISTIC#
         AND SESS.SID = STAT.SID
         AND B.NAME = 'opened cursors current'
GROUP BY SESS.USERNAME, SESS.MACHINE
ORDER BY 1 DESC;

bumping up OPEN_CURSORS should help? ask the DBA! and also reevaluate whatever you are trying to do

SQLOracle