• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

JDBC Max number of cursors exceeded issue

 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
author
Posts: 4173
29
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Please do not shoot the fish in this barrel. But you can shoot at this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic