File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes maximumm cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "maximumm cursors exceeded" Watch "maximumm cursors exceeded" New topic

maximumm cursors exceeded

anagha patankar
Ranch Hand

Joined: Dec 26, 2005
Posts: 53
While we did one implementation I would get this exception :
scenario was we were reading line by line from a file and would insert records into the database

The file could run into thousands of records .

so for each line in file was inserting a record in the database

suddenly in the middle of the run - it would throw the error
However I was expecting that after this error - I would not be able to insert any more records .

Surprisingly - a few records would be skipped and then again it would start inserting records .

I had ensured that all resultsets / statements / connections were closed .

Another question :

In order to replicate here is what I have done :

1 >
written a for loop where I run the loop 100 times
in each iteration just do a select from a table
DO NOT CLOSE the prepared statement

I get the exception maximumm cursors exceeded

2 >
Oracle configured for max no of cursors = 50 per session

3 >
On running the following command :
SELECT * FROM v$open_cursor order by sid,user_name

This returns no of records > 50 for specific sid

This does explain this exception to some extent

Here are my additional doubts :

a >
After I get this exception :
On shutting down the app server - the no of open cursors decreases
so looks like in some way the open cursors get closed .
How ? what is the mechanism here ?

b >
Lets say that such bad code does exist and as stated above - max cursors per session = 50

Lets say that due to such bad code - for every session 40 cursors remain open and not closed

since the no of open cursors are less that max alllowed
user will be able to navigate through app

what would happen where lets say 1000 users accessed the application

1000 * 40 = 40,000 open cursors would exist in db ( independent of sid )

would the database performance get affected with that many open cursors ?

Thanks for everyones patience .


Thanks in advance,
[ December 28, 2005: Message edited by: anagha patankar ]
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

would the database performance get affected with that many open cursors ?

Cursors are database resources, and if you keep using them without returning them, eventually you will run out. Without any references I can point you at, I believe that yes, it is a problem, and yes, you will need to fix the code if it is causing a resource leak.
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
a> Open Cursors is a limit at DB session level. This maps to a JDBC connection on Java. When you get a max open cursors exceeded exception, that is for a specific connection which being used. Other threads using different connections to the same database, which haven't hit that limit will continue as normal.
When you shut down the app server, you have closed the JDBC connections and so those open cursors have gotten closed.

b> When you have 1000 users accessing the application, you'll probably have fewer pooled JDBC connections being used. So if you return a connection which has exceeded the limit back to the pool, it will be unusable when someone gets it again from the pool, and either the cursors need to be closed or the connection needs to be closed.
Oracle can easily handle thousands of sessions with hundreds of open cursors in each session. However if reopening the same cursor multiple times uses unnecessary resources, reducing efficiency. Further it can cause chaining on some of the internal hash tables because your cursors are identical causing increased resource usage and contention in the library cache.

<a href="" target="_blank" rel="nofollow">The Peak of Performance</a>
I agree. Here's the link:
subject: maximumm cursors exceeded
jQuery in Action, 3rd edition