ORA-1000 errors, "Maximum open cursors exceeded."

==============================
General
==============================
Each session can open many cursors.
Open cursors are kept in the shared pool, in the library cache.
Optionally cursors can be cached in a session level, as to improve performance for reused cursors. 

If session attempts to open more cursors than the limit set by OPEN_CURSORS parameter, ORA-01000 maximum open cursors exceeded is thrown.

=======================
Theory
=======================

What is cursor?
When cursor is opened?
When does cursor should be closed opened?
Working with jdbc
What is cursor?
Per Oracle documentation:
Cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). 
By default Cursor is implicit.
Cursor can be explicit when it is declared in PL/SQL block.

When cursor is opened?
Cursor is opened per each SQL execution.
Explicit cursors are opened explicitly.

When cursor is closed?
For implicit cursors, Oracle handles cursor closing.
For explicit cursors, there are several options. 

Per Oracle documentation:
When you declare a cursor in a package (that is, not inside a subprogram of the package) and the cursor is opened, it will stay open until you explicitly close it or your session is terminated.

When the cursor is declared in a declaration section (and not in a package), Oracle Database will also automatically close it when the block in which it is declared terminates. 

It is still, however, a good idea to explicitly close the cursor yourself. 

So Global Cursors, would stay opened, until closed explicitly. 
Or when the session terminates.

Working with jdbc
When working in jdbc, each java.sql.ResultSet is a new cursor in Database.
Thus if java.sql.ResultSet is not being closed explicitly in code, the session would run out of allowed open cursors, and ORA-01000 would happen.

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

Initialization Parameters
==============================

There are two main initialization parameters that affect cursors.
One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.

SELECT name, value from V$PARAMETER WHERE name like '%cursor%';
NAME                           VALUE
------------------------------ ------------------------------
session_cached_cursors         100
open_cursors                   300

To change the parameter:
ALTER SYSTEM SET OPEN_CURSORS=400;
ALTER SYSTEM SET OPEN_CURSORS=400 SCOPE=BOTH;

OPEN_CURSORS

OPEN_CURSORS parameter control the maximum number of cursors each session can have open, For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. 

If a single session has reached the limit of OPEN_CURSORS, it will get an ORA-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS:

Default value in init.ora is 300.

If that value is not set in init.ora, then it is 50.

Tom Kyte recommends setting it to a high value, around 1000.

SESSION_CACHED_CURSORS

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. 

Reasonable value for SESSION_CACHED_CURSORS would be one third of OPEN_CURSORS value.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. Your cursors will still be cached in the shared pool, and your session will have to find them there.

If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache.

Since a session doesn't have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems.

In the session cursor cache, Oracle manages the cached cursors using a LRU list. 

Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.

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

Monitoring open cursors

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

Main tables:

V$OPEN_CURSOR

V$SESSTAT and V$STATNAME for 'opened cursors current' statistic

V$OPEN_CURSOR

Displays data per session level

Shows cached cursors - NOT not currently open cursors.

V$SESSTAT and V$STATNAME for 'opened cursors current' statistic

To monitor open cursors, query V$SESSTAT for statistic 'opened cursors current'. 

This will give the number of currently opened cursors, by session.

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

Useful SQLs by Example

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

A. Total cursors open, by session

SELECT VSTATNAME.name,
       VSESSTAT.value, 
       VSESSION.username, 
       VSESSION.sid, 
       VSESSION.serial#
FROM   V$SESSTAT VSESSTAT, 
       V$STATNAME VSTATNAME, 
       V$SESSION VSESSION
WHERE VSESSTAT.statistic# = VSTATNAME.statistic#  
  AND VSESSION.sid=VSESSTAT.sid
  AND VSTATNAME.name = 'opened cursors current'
ORDER BY VALUE DESC;  

NAME                    VALUE USERNAME                     SID    SERIAL#

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

opened cursors current    300 AUS_APPSA_USERA               451      6789

opened cursors current     85 AUS_APPSA_USERA               584     46421

opened cursors current     61 AUS_APPSA_USERA               594     40116

opened cursors current     60 AUS_APPSA_USERB               465     36373

opened cursors current     60 AUS_APPSA_USERA               454     31063

opened cursors current     59 AUS_APPSA_USERC               568     24066

opened cursors current     58 AUS_APPSA_USERD               427      2505

opened cursors current     58 AUS_APPSA_USERA               629     29818

opened cursors current     58 AUS_APPSA_USERA               569     13840

opened cursors current     57 AUS_APPSA_USERA               616      3877

B. Total cursors open, by user, server

SELECT SUM(a.value) total_cur, 

       ROUND(AVG(a.value),2) avg_cur, 

  MAX(a.value) max_cur,  

  s.username, 

  s.machine

 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' 

GROUP BY s.username, s.machine

ORDER BY 1 DESC; 

 TOTAL_CUR    AVG_CUR    MAX_CUR USERNAME         MACHINE

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

      2157      31.26         44 AUS_APPSA_USERA  aus-app-2-srv-2

       804      50.25         61 AUS_APPSA_USERB  aus-app-2-srv-2

       511      31.94         43 AUS_APPSA_USERC  aus-app-2-srv-1

       300         60        300 AUS_APPSA_USERD  aus-app-2-srv-6

       116      19.33         85 AUS_APPSA_USERE  aus-app-2-srv-1

        29       1.12         19 AUS_APPSA_USERF  aus-app-2-srv-3

        14        .93          1 AUS_APPSA_USERG  aus-app-2-srv-1

        14        .93          1 AUS_APPSA_USERH  aus-app-2-srv-3

C. Check if open cursors reach the limit set by OPEN_CURSORS
SELECT MAX(a.value) as highest_open_cur, 
       p.value as max_open_cur
  FROM V$SESSTAT a, V$STATNAME b, V$PARAMETER p
 WHERE a.statistic# = b.statistic#
   AND b.name = 'opened cursors current'
   AND p.name= 'open_cursors'
GROUP BY p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- -------------
             300 400


D. Monitor the number of cursors each session has in its session cursor cache.
SELECT a.value, s.username, s.sid, s.serial#
  FROM V$SESSTAT a, V$STATNAME b, V$SESSION s
 WHERE a.statistic# = b.statistic#  
   AND s.sid=a.sid
   AND b.name = 'session cursor cache count';

 

     VALUE USERNAME                      SID    SERIAL#
---------- ---------------------- ---------- ----------
       100 AUS_APPSA_USERA               583       5254
       100 AUS_APPSA_USERA               584      46421
       100 AUS_APPSA_USERA               586      31312
       100 AUS_APPSA_USERC               587      60148
       100 AUS_APPSA_USERA               588      60429
       100 AUS_APPSA_USERB               590      64872
        99 AUS_APPSA_USERA               591      20407
        99 AUS_APPSA_USERC               592       4227
        99 AUS_APPSA_USERE               594      40116
        99 AUS_APPSA_USERA               595      23655

E. See the SQL Text of session cached cursors.

SELECT SESSIONS.user_name, 
       SQLAREA.sql_text
 FROM V$SESSION SESSIONS,
      V$SQLAREA SQLAREA
WHERE SESSIONS.sql_id = SQLAREA.sql_id  
  -- for 9i and earlier use: SESSIONS.address = SQLAREA.address

  AND SESSIONS.sid||'-'||serial# IN ('583-5254', '584-46421');

or:

SELECT CURSORS.user_name, 
       SQLAREA.sql_text
 FROM V$OPEN_CURSOR CURSORS, 
      V$SQLAREA SQLAREA
WHERE CURSORS.sql_id = SQLAREA.sql_id  
  -- for 9i and earlier use: CURSORS.address=sql.address
  AND CURSORS.sid=451;

USER_NAME          SQL_TEXT
------------------ ---------------------------------------------------------
AUS_APPSA_USERA    SELECT GIMSI.PLMN_CODE, GIMSI.NETWORK_ID, NET.NETWORK_NAME, 
AUS_APPSA_USERA    BEGIN START_ETL_PROCESS.removeDBStreamTag(); END;           
AUS_APPSA_USERA    SELECT NVL(AVG(SSSA.AVG_VOICE_USAGE),0) FROM SGA_SUBSCRIBER 
AUS_APPSA_USERA    select owner#,name,namespace,remoteowner, linkname, 
AUS_APPSA_USERA    select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,     
AUS_APPSA_USERA    select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0), 
AUS_APPSA_USERA    update GA_SYNC_TABLE set IS_SYNCED_FLAG = 0, 
AUS_APPSA_USERA    select /*+ index(idl_char$ i_idl_char1) +*/ 

F. See the SQL Text of open cursors and the calling session info.
SELECT SESSIONS.sid, 
       SESSIONS.serial#, 
       SESSIONS.paddr, 
       SESSIONS.status, 
       SESSIONS.username,
       SESSIONS.osuser, 
       SESSIONS.machine, 
       SESSIONS.program, 
       SESSIONS.module, 
       SESSIONS.logon_time, 
       OPEN_CURSORS.sql_id, 
       OPEN_CURSORS.sql_text
FROM V$SESSION SESSIONS,
     V$OPEN_CURSOR OPEN_CURSORS
WHERE SESSIONS.sid = OPEN_CURSORS.sid
  AND SESSIONS.saddr = OPEN_CURSORS.saddr
  AND SESSIONS.user# <> 0
  AND SESSIONS.sid = 146
--  AND SESSIONS.saddr = '87E78A5C'

G. Tuning number of session cached cursors.

See the number and percent of parses from session cursor cache vs parses from library cache.

SELECT cach.value SESSION_CACHE_HITS,

       prs.value TOTAL_PARSES,

       ROUND((cach.value/prs.value)*100,2) AS CACHE_HITS_PCT

  FROM v$sesstat cach, 

       v$sesstat prs, 

       v$statname nm1, 

       v$statname nm2

 WHERE cach.statistic# = nm1.statistic#

   AND nm1.name = 'session cursor cache hits'

   AND prs.statistic#=nm2.statistic#

   AND nm2.name= 'parse count (total)'

   AND prs.sid= cach.sid

   AND cach.sid=624; 

SESSION_CACHE_HITS TOTAL_PARSES CACHE_HITS_PCT

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

                65           86          75.58

==============================
Reference
==============================

Excellent and elaborate article on this issue: Monitoring Open and Cached Cursors

=========================================
Simple 3 step debug for open cursors issue
=========================================
--------------------------------
--Get sid with top open cursors
--------------------------------
select  SESSIONS.sid, SESSIONS.serial#, SESSTAT.value, SESSIONS.username
 from V$SESSTAT  SESSTAT, 
      V$STATNAME STATNAME, 
      V$SESSION SESSIONS
where SESSTAT.statistic# = STATNAME.statistic#  
  and SESSIONS.sid=SESSTAT.sid
  and STATNAME.name = 'opened cursors current'
  and SESSIONS.username is not null;

       SID    SERIAL#      VALUE USERNAME
---------- ---------- ---------- -------------------
        88       3751          2 V700_87
        89       2093          1 V700_87
       121      55027          6 TO_UPGRADE_V500_1

       892       7241          1 V700_87
--------------------------------
--Get sql of the sessions returned in step A
--------------------------------
select  sid , sql_text, USER_NAME, count(*) as OPEN_CURSORS
 FROM V$OPEN_CURSOR 
WHERE sid IN (121) 
group by sid ,sql_text, USER_NAME
having count(*) > 1
order by OPEN_CURSORS DESC


       SID SQL_TEXT                                                     USER_NAME                      OPEN_CURSORS
---------- ------------------------------------------------------------ ------------------------------ ------------

       121 SELECT NVL(CONF.PROPERTY_VALUE, DEF.PROPERTY_DEFAULT_VALUE)  TO_UPGRADE_V500_1                         3

--------------------------------
--See if parameter open_cursors is set accordingly
--------------------------------

SELECT  MAX(SESSTAT.value) as highest_open_cur, 
        PARAMETER.value as max_open_cur 
FROM    V$SESSTAT SESSTAT, 
        V$STATNAME STATNAME, 
        V$PARAMETER PARAMETER 
WHERE SESSTAT.statistic# = STATNAME.statistic#  
  and STATNAME.name = 'opened cursors current' 
  and PARAMETER.name= 'open_cursors' 
group by PARAMETER.value;


HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------

              17 300





COL value for 999999
COL username for A30
COL machine for A30
COL program for A20
COL spid for A10
COL sql_id for A20

set pagesize 1000
set linesize 120

SELECT V_SESSTAT.value,
       V_SESSION.username,
       V_SESSION.sid,
       V_SESSION.serial#,
       V_PROCESS.spid,
       V_SESSION.machine,
       V_SESSION.program,
       V_SESSION.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 > 100;


   


  VALUE USERNAME                              SID    SERIAL# SPID       MACHINE                        PROGRAM              SQL_ID
------- ------------------------------ ---------- ---------- ---------- ------------------------------ -------------------- --------------------
    270 GBR_EVERY_IPNQQ                       981         57 17792      vrtsapuld12                    JDBC Thin Client
    173 GBR_EVERY_IPNQQ                       387        231 21353      vrtsapuld13                    JDBC Thin Client
    135 GBR_EVERY_IPNQQ                      1179        281 17135      1                              JDBC Thin Client
    183 GBR_EVERY_IPNQQ                       773        489 21550      vrtsapuld14                    JDBC Thin Client
    237 GBR_EVERY_IPNQQ                       206       1209 18255      vrtsapuld12                    JDBC Thin Client
    238 GBR_EVERY_IPNQQ                      1171         43 20442      vrtsapuld13                    JDBC Thin Client
    239 GBR_EVERY_IPNQQ                       972       4059 21951      vrtsapuld14                    JDBC Thin Client