We have been getting the ora01000 Maximum Open Cursor Exceeded exception in production. our production environment oracle 10g(RAC). Max open cursor limit set to 300.
Front End applicaiton is Asp.Net
when user searching on booking details,application calls the oracle stored procedure it return REF CURSOR as OUTPUT parameter.
During this time, the above exception has occurred.
we have simulated in Dev Environment, (oracle 10g Standalone DB) Max open cursor set to 300
Test case 1:
single user logged via application, searching booking details, we have been monitoring Cursor usage from Database,we observed that session shoots up to 299 cursor we expected the exception but it didn't occurred rather it invoked a new session and proceeded further. why this behaviour?
Test Case 2:
Ten users simultaneously logged in searching the same page doing similar operations from database we have monitored 10 user session invoked. only 2 users got the exception
Test Case 3:
From database we directly executed the Procedure which application using in search page, we observed that only minimal cursor opened
Clarification
Where the issue will be either in application or in Database. Please suggest us how to resolve the issue.
Thanks