We got No of process exceed error on Oracle database. I went through my JDBC code. There I not deallocated recoursec
. . .
will it be a reason of error we r getting on oracle database ?? What are other problem come up if resources are not deallocated ?
Open cursors use space in the library cache in the shared pool. The library cache and shared pool are 'shared' by all sessions. The more space used by one sessions open cursors the less space there is for other sessions.
A major purpose of the shared pool is to cache SQL statements that will be reused. When a statement needs to be cached and there is no room older statements may be aged out to make room. If the statements that are aged out need to be reused they will now have to be reparsed when they are executed again.
So the aging out/reparse of items uses resources that are unnecessary, especially if statements are cached that should really aren'te needed anymore and should have been closed.
See the article for a detailed explanation of how the shared pool is tuned and used
This Orafaq blog about 'Monitoring Open and Cached Cursors' is also pretty easy to understand and shows how to tell how many cursors are open or cached
And see Tom Kyte's first reply in this AskTom article. It includes simple Java sample code that shows how the cursor count can change and that even when your code closes a cursor it may still be reflected in the open cursor count if you query Oracle.
v$open_cursor can be misleading -- it is cursors that have been opened at some point and
may (or may not be) still open. It is useful in helping to track down cursor leaks --
but it shows you more then just "really truely open" cursors.
The Javadoc says clearly that you have to close Cursors, ResultSets, Statements, and Connections. So close them. The consequences of not closing them aren't defined. So they could certainly include the behaviour you mention. Even if they don't, you have uncovered a serious flaw in your application code that must be fixed. So fix it.