[Oracle][ODBC][Ora]ORA-01000: maximum open cursors exceeded ORA-06512

Hi venky1981,

Close all connections properly after completing the execution...

Regards,

Suresh

www.aspiredba.com

The initialization parameter OPEN_CURSORS (typical value for a huge database is 600) in INITSID.ORA determines the maximum number of cursors per user.
Check the parameter specified by executing the following SQL:
select * from v$parameter
where name = 'open_cursors'
/

If you want more cursors to be opened at the same time, shut the database, change INITSID.ORA and restart the database.

The cursors that are counted for this are those explicit cursors that you opened and never closed or the cursors the PL/SQL keeps open. If you use a lot of stored procedures, then you will see lot of cached cursors. From release 8.1, PL/SQL will close these cached cursors on commit.

You can find the list of open cursors and the users who opened them by executing the following SQL:

select user_name, status, osuser, machine, a.sql_text
from v$session b,
v$open_cursor a
where a.sid = b.sid
/

But the above SQL will tell you about cursors opened at some point of time, but does tell you about currently open cursors. But the above SQL will helps us to track cursor leaks, which would need fixing, to avoid this error in the future.

The SQL given below will tell you how many are open truly.

select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
/

Always take care to CLOSE a cursor before your procedure ends.

best of luck.

Gaurav

Hi Venky1981,

I read a very good commentary on a similar question by schwertner at this thread, may give you some more insight :

https://www.experts-exchange.com/questions/20563087/ORA-01000-maximum-open-cursors-exceed.html

SDutta

To share my experience…
I faced similar situation while working in a VB application where the application, after running for certain period of time was ABENDing with this oracle error.
I had to explicitly close all the record sets and connections once they are not required to get rid of this error.
Changing the INIT.ORA file may help but cannot resolve the issue forever.

I've tried closing all result sets before. And I still got the max cursors exceeded errors. As mentioned, bumping up the cursor
limit is only a temporary solution. There is only one solution that I found - that is to close your connections periodically.
When a connection is closed, all the associated resources (such as cursors) are also closed.

I've experimented a bit
        with this - you can set an arbitrary "chunk" size. When you hit this chunk size you can close all connections and
        restart. When you close a connection, it will close all associated resultsets, cursors etc which will free up some
        resources.
            eg.
       // Loop counter
       int count = 0;
       // To indicate when to clean up connection and start afresh
       int chunks = 20; // although this size is aribtrary, but found to be a good setting
 
       for (int i=0; i<100;i++)
       {
        count++;
        if ((count % chunks) == 0)
        {
             if (conn != null && !conn.isClosed()) conn.close();
             conn = ConnectionManager.connect(database_url_string);
        }  
       }

Hope that helps.

Close all the statemenets opened in your code .........