We have a Java client talking to the database using JDBC.
I have noticed that after we run heavy traffic on our database using the client for a while, I get "Max Cursors Exceeded" error. Increasing the "processes" parameter in init.ora to about 300 will postpone this problem. We are able to run more traffic, but then after some more time, the same error occurs.
This error also occurs if many users talk to the database at the same time using the java clients on their machines.
Can anyone please tell me what the processes parameter in init.ora does ? Is there a way to resolve this problem ? I am at a dead end.
When you set the processes it would specify the number of operating system user processes that can connect to the database. This would inturn also set the number of sessions (1.1 * processes + 5) and the transactions. This value would include the background processes (around 9 to ...)
When you set the open_cursors, it would specify the maximum number of open cursor that a session can have at once. This value can range from 1 to (4 GB -1) on 8.1.6. This parameter would only prevent the sessions from opening excessive number of cursors. So choose a good high number, that you think would elliviate this cursor shortages.
In the mean time, also make sure that your application after opening the cursor, would not forget to close the cursors. If the application were to fail in closing the cursor, then the cursor handle would be alive until the session that open that cursor were to die or close.
I hope this would have given you some idea on how things work.