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

Posted on 2003-03-28
Medium Priority
Last Modified: 2012-08-14
I am using an oracle database as the back end for my asp.things were working fine when out of the blue i get the following error:

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

i tried changing the setting in the init.ora file and also restarted my machine.is there anything else i need to change.also i hadnt made any changes to the stored procedures that i am using...so i am really surprised that i got the error.

any ideas???
Question by:venky1981
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 53

Accepted Solution

Ryan Chong earned 140 total points
ID: 8224510
It's bcos the connection is not close until hit the limit, so the better approach will be closing the connection that not using when it's not necessary.

Expert Comment

ID: 8224812
Hi venky1981,

Close all connections properly after completing the execution...




Expert Comment

ID: 8225003
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Expert Comment

ID: 8225374
Hi Venky1981,

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



Expert Comment

ID: 8227622
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.


Expert Comment

ID: 8566007
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
       // 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++)
        if ((count % chunks) == 0)
             if (conn != null && !conn.isClosed()) conn.close();
             conn = ConnectionManager.connect(database_url_string);

Hope that helps.

Expert Comment

ID: 9335808
Close all the statemenets opened in your code .........

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question