Error ORA-01000 Too many open cursors is coming, and it is not clear why.

==========

General

==========

Error ORA-01000 Too many open cursors is coming, and it is not clear why.

Code below is logging number of cursors for current session, and can be called from various places in code.

CREATE SEQUENCE DEBUG_OPN_CURSORS_ID_SEQ NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE; 

CREATE TABLE DEBUG_OPEN_CURSORS (
  entry_id   NUMBER,
  sid        NUMBER,
  machine    VARCHAR2(64 BYTE),
  user_name  VARCHAR2(30 BYTE),
  sql_id     VARCHAR2(13 BYTE), 
  sql_text   VARCHAR2(1000 BYTE),
  open_cursors NUMBER,
  comments   VARCHAR2(1000 BYTE)  
  )
TABLESPACE IGT_TABLE ;
  
AS SYS !!  
GRANT SELECT ON SYS.V_$OPEN_CURSOR TO SPARX_700;
GRANT SELECT ON SYS.V_$SESSION TO SPARX_700;
GRANT SELECT ON SYS.V_$SQLAREA TO SPARX_700;

-----------------------
--procedure write_cursors

-----------------------

create or replace procedure write_cursors(p_comments IN VARCHAR2, p_username IN VARCHAR2)  is

pragma autonomous_transaction;

begin

  

  INSERT INTO DEBUG_OPEN_CURSORS (entry_id, sid, machine, user_name, sql_id, sql_text, open_cursors, comments)

  SELECT DEBUG_OPN_CURSORS_ID_SEQ.nextval, sid, machine, user_name, TOP_OPEN_CURSORS_SESSIONS.sql_id, V$SQLAREA.sql_text, open_cursors, p_comments

   FROM (

  --Get sessions with open cursors 

  SELECT SESS.sid sid,  

         SESS.machine machine,  

         OPEN_CURS.user_name user_name,        

         OPEN_CURS.sql_id sql_id,

         count(*) open_cursors

  FROM v$open_cursor OPEN_CURS, 

       v$session SESS

  WHERE OPEN_CURS.saddr = SESS.saddr

    AND SESS.username IS NOT NULL  AND SESS.username NOT IN ('SYS')  

    AND (p_username IS NULL OR (p_username IS NOT NULL AND SESS.username = p_username ))    

    AND OPEN_CURS.user_name IS NOT NULL AND OPEN_CURS.user_name NOT IN ('SYS') 

    AND (p_username IS NULL OR (p_username IS NOT NULL AND OPEN_CURS.user_name = p_username ))

  GROUP BY SESS.sid,  SESS.machine, OPEN_CURS.user_name, OPEN_CURS.sql_id

  HAVING COUNT(1) > 1

  ) TOP_OPEN_CURSORS_SESSIONS,

  V$SQLAREA

  WHERE TOP_OPEN_CURSORS_SESSIONS.sql_id = V$SQLAREA.sql_id(+)

  ;  

  commit;

end write_cursors;

-----------------------

 

BEGIN

 write_cursors('Step A', 'MY_SCHEMA');

 write_cursors('Step B', 'MY_SCHEMA');

 write_cursors('Step C', 'MY_SCHEMA');

END;

/

====================

Get SQL Text of current running SQLs

====================

SELECT sql_text, sql_id, executions, fetches, end_of_fetch_count

FROM V$SQLAREA 

WHERE sql_id IN (

SELECT NVL(sql_id,prev_sql_id) sql_id

 FROM (

SELECT V_SESSTAT.value OPEN_CURSORS,

       V_SESSION.username,

       V_SESSION.sid||'-'||V_SESSION.serial# sid_serial,

       V_PROCESS.spid,

       V_SESSION.machine,

       V_SESSION.program,

       V_SESSION.sql_id,

       V_SESSION.prev_sql_id       

  FROM V$SESSTAT V_SESSTAT,

       V$STATNAME V_STATNAME,

       V$SESSION V_SESSION,

       V$PROCESS V_PROCESS

 WHERE V_SESSTAT.statistic# = V_STATNAME.statistic#

   AND V_PROCESS.addr=V_SESSION.paddr

   AND V_SESSTAT.sid = V_SESSION.sid

   AND V_STATNAME.name = 'opened cursors current'

   AND V_SESSTAT.value > 1

   )

)

Executions - Total number of executions, totalled over all the child cursors

Fetches - Number of fetches associated with the SQL statement

End of Fetch Count - Number of times this cursor was fully executed

                     The value of this statistic is not incremented when the cursor is partially executed, 

     either because it failed during the execution or 

     because only the first few rows produced by this cursor are 

                     fetched before the cursor is closed or re-executed.

================================

A script to sample open cursors per session

================================

gen_cursors_list.sql

COL sid FOR A10

COL machine FOR A30

COL program FOR A30

col USER_NAME FOR A30

COL sql_id FOR A20

COL sql_text FOR A200

COL run_date FOR A20

COL open_cursors FOR 99999999

SET PAGESIZE 1000

SET LINESIZE 1000

SET heading on

SET feedback off

spool open_cursors_list.txt append

SELECT sid, 

       spid, 

       open_cursors, 

       machine, 

       program, 

       user_name, 

       TOP_OPEN_CURSORS_SESSIONS.sql_id, 

       V$SQLAREA.sql_text, 

       TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') as run_date

   FROM (

  --Get sessions with open cursors

  SELECT SESS.sid sid,

         PROC.spid spid,

         SESS.machine machine,

         SESS.program program,

         OPEN_CURS.user_name user_name,

         OPEN_CURS.sql_id sql_id,

         count(*) open_cursors

  FROM v$open_cursor OPEN_CURS,

       v$session SESS,

       v$process PROC

  WHERE OPEN_CURS.saddr = SESS.saddr

    AND SESS.username IS NOT NULL AND SESS.username NOT IN ('SYS')

    AND OPEN_CURS.user_name IS NOT NULL AND OPEN_CURS.user_name NOT IN ('SYS')

    AND PROC.addr=SESS.paddr

  GROUP BY SESS.sid, PROC.spid, SESS.machine, SESS.program, OPEN_CURS.user_name, OPEN_CURS.sql_id

  HAVING COUNT(1) > 10

  ) TOP_OPEN_CURSORS_SESSIONS,

  V$SQLAREA

  WHERE TOP_OPEN_CURSORS_SESSIONS.sql_id = V$SQLAREA.sql_id(+);

spool off
EXIT;

gen_cursors_list.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

sqlplus DEU_TDGQQ_SBQQQ/"Deu_#123_SBQQQ$"@rraf @gen_cursors_list.sql