Java developer said that when he run sql statement embeded with Java , he got the following error messages.
The sql statement is the very simple insert and update statement.
Ora - 01000
01000, 00000, "maximum open cursors exceeded"
"error occurred at recursive SQL level %s"
// *Cause: An error occurred while processing a recursive SQL statement
// (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.
I checked open_cursor parameter in init.ora file.
It wat set to 200. I think that It seems OK.
I issued the following query
select * from v$open_query and only returns 14 rows.
I don't know what to do except increase the open_cursor parameter. This will be the last choice since the production database(24*7).
What do you think about this?
Are you talking about v$Open_cursor? I don't have a v$Open_query in my 8.1.6 database.
If so, v$open_cursor does not show recursive cursors that are open. These are internal queries that Oracle has to run to complete your queries. There may be enough internal queries that are using up your limit on open cursors.
I don't know of a way around this other than to change the init.ora parameter, but there may be a way that I am unaware of. ( I do know that you can't change this parameter on the fly with an 'alter system' command)
If we close all the cursors that are opened in our stored procedures will that not work ? The reason is, when we run traffic on our database trying to see how it withstands heavy load, it often gives this max cursors exceeded error. Increasign the init.ora only postpones the error. And the solution described above would require user intervention. Isnt there something automatic ?
I have experienced this before, believe me there must be something wrong within your code that the opening cursor can not be "closed" for some reason. Always check your code or logic before increasing or closing the cursor manually. The problem can not be solved just by increasing the max cursors sometimes, and this increased "big max cursor value" probably exceeded without carefully coded.
I agree with you.
All oracle documentation and my research tells me that Java developer didn't close cursor appropriately.
I told the developer the importance of the closing cursor, but he said that he closed all the cursor explicitly.
Since I don't know the Java programming at all, I can not say anything more. what can i say?
Next time, when he runs the same query again, I want to issue select * from v$open_cursor to see the how many curosor and which statement opens the cursor without closing it.
We have faced the same problem here in our company. The problem however turned out to be that in the java code, the code always had stmt.close() missing form a lot of cases where they had executed a query. When this was added, the problem went away.