Solved

ORA-01000: maximum open cursors exceeded

Posted on 2010-11-29
12
1,942 Views
Last Modified: 2012-06-27
I have an application that iterates through a flat file and parses out some fields and inserts those fields into a table.  I have previously run this in a test environment and inserted over 8K rows without a problem.

The Oracle running on the server was updated and now I can only insert 444 rows.  I make it a point to close connections and command objects but still have this problem.

Please advise.
0
Comment
Question by:hbash
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 63 total points
ID: 34232466
Are you reusing the same connection object for the insert or are you creating a new object for each insert? Can you post an excerpt of how you interact with the DB?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 34232561
Did you know Oracle comes with a program to do this.  It's called SQL*Loader.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34232657
You might just need more cursors:

select * from v$parameter where name='open_cursors';

Open in new window


To raise the parameter:

ALTER SYSTEM SET open_cursors = 1000;

Open in new window


I agree with the previous comment that SQL*Loader or (preferrably) external tables are the way to go for what you are trying to accomplish.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 34232690
You should not need to increase the open_cursors parameter for 444 rows.  It's likely a logic bug that kaufmed is hinting at.
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 63 total points
ID: 34233088
It depends on how many cursors he has left when executing the code, and on how the code does it.

For instance: If he used bind variables, he'd use only one cursor. So chances are he's using dynamic SQL to concatenate the values and execute an INSERT. That's bad practice for a number of reasons, including increased parse times, more shared memory used, and opening the door to SQL injection.

The best solution would be to rewrite the whole thing using external tables or else SQL*Loader. However, a quick fix is to increase the parameter... and not a bad one: If he's only 444 cursors from exhausting the parameter, he may hit that limit sooner or later on other portions of code anyway.

Adjusting CURSOR_SHARING is also a nice possibility, but it would need some expertise to perform exhaustive testing before changing it instance-wide. It is definitely a good one to recommend doing this before the INSERT loop:

alter session set cursor_sharing=force;

Open in new window


But again... 444 cursors from hitting the limit can be too few IMHO.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 124 total points
ID: 34233181
Even if you are not using bind variables, which I agree is bad practice, the cursor should not remain open past the insert statement running.

Also OPEN_CURSORS is a session level parameter.  Why would a single session ever need 1000 open cursors?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:hbash
ID: 34233964
I close and dispose of all connection and command objects per row (added to see if it corrects this).  Also,  in my googling I see that Oracle "apparently" has some issues with freeing up connections even after the underlying code requests this.  Many matches for this error with most responses alluding to a problem with Oracle in this regard.

Regarding modifying the server connections: it's a production server and I cannot do that.

In sniffing around more for answers what I found that seems to work (I will do more testing) is to every say 200 inserts,  I call garbage collection for the vb.net application and that somehow does the trick.  Not clear what is happening to make Oracle release these connections, but have successfully inserted 9K rows with this addition of calling garbage collection.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 34234000
I've not seen this problem when you manually call dispose after close.  I would verify that you are disposing all objects and not missing one.

I've also run into this.  for some reason Oracle's clean up still sees .Net connections as 'live' even though .Net has closed it and will never release it.  To date, as long as I make sure I dispose the objects, I've not run into the problem again.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 124 total points
ID: 34234016
I don't know a lot about the application side.  Are you using connection pooling?  I have run into issues with various connection pooling applications where they are not correctly cleaned up.  That is not an issue with the database, that is an issue with the connection pool.  You request the connection to be closed, but the pool leaves it open hoping to reuse it, it never does and causes this problem.
0
 
LVL 1

Accepted Solution

by:
hbash earned 0 total points
ID: 34236202
I have searched/checked for every connection object and command object that I create and associate with the database connection.  I have tried having one global connection object set to the database and also the one connection object closed and reopened for each access and I always blow with an exceeded max connections error message.

The only thing that seemed to "fix" this problem was calling GC.Collect().
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 34238495
It might be a bug of some type.  

To further assist you we would need version numbers of the database, client and version/type of data access provider you are using as well as some code.
0
 
LVL 1

Author Closing Comment

by:hbash
ID: 34289824
All good suggestions.  However te GC.Collect() call worked.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now