File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC Max number of cursors exceeded issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Max number of cursors exceeded issue" Watch "JDBC Max number of cursors exceeded issue" New topic
Author

JDBC Max number of cursors exceeded issue

anagha patankar
Ranch Hand

Joined: Dec 26, 2005
Posts: 53
Hello,
A few months back we faced an issue where in the stack trace we saw an exception specifying the maximumm number of cursors are exceeded

I do understand why this exception is caused ( not closing prepared statement )
Have since rectified the issue.

My query is - after we get the exception :
1 >At what point are the cursors closed in Oracle database?
2 >Assuming that this exception would occur when the user was posting some data from UI - what would happen the second time the user tried to post again ?
would we get the same exception again ?
3 >Does the application become unusable without a bounce of application server
4 >Does DBA have to phycically close the cursors?

Thanks,
anagha
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

They are closed when you execute the connection.close() in your Java Code. Failure to do so (such as if an exception is thrown) will leave the connection open possibly leading to maximum cursors exceeded. That is why you should put close() calls in a finally block.


My Blog: Down Home Country Coding with Scott Selikoff
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
I am not a dba but I know something about Oracle.
1 >At what point are the cursors closed in Oracle database?

There is a background process called PMON in oracle, which is said to be responsible of collecting killed or timed out sessions.
2 >Assuming that this exception would occur when the user was posting some data from UI - what would happen the second time the user tried to post again ? would we get the same exception again ?

I suppose that exception is max open cursor limit is reached, by the way which is defined in init.ora file of Oracle. This happens when you request a new connection. So if you try to reconnect every time you want to execute a transaction yes you would get the same exception unless some other(s) have returned the connection by closing it so there is available limit for your new connection request.
3 >Does the application become unusable without a bounce of application server

I you do give some break for internal Oracle background processes they may close invalid sessions, but if you are always off the limits you probably need to increase open_cursor in init ora file and consider revising applications that refer to Oracle connections and don't return it! I again suppose you meant database server by application server.
4 >Does DBA have to phycically close the cursors?

Yes, dba can kill the sessions and mark them to be collected by PMON by execution following pl/sql code piece.
find sessions -better to use some tool like toad or pl/sql developer

kill desired session
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Max number of cursors exceeded issue