[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
LVL 55

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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

616 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