| ORA-01000: maximum open cursors exceeded [message #91355] |
Fri, 26 July 2002 02:45  |
Martin
Messages: 83
Registered: February 2000
|
Member |
|
|
I am working on a java program that transacts with an Oracle 9i database using jdbc thin driver. SuSe Linux.
I am getting the "ORA-01000: maximum open cursors exceeded" message even though the number of open cursors is 7 ( much less than the open_cursors = 300 in my init.ora).
I used the following sql to view the number of open cursors:
select user_name, status, osuser, machine, a.sql_text
from v$session b,
v$open_cursor a
where a.sid = b.sid
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
My program continually loops through a set of data and opens resultsets in the database. Depending on the content of the resultsets, it will either update or insert records in to the database. I have ensured that the resultsets are closed at the end of each loop.
I have also tried closing the prepared statements that generate the resultsets in the loop. This improves the situation slighlty by increasing the number of transactions that occurr before the error occurs.
I can trace the program to the point where the exception is thrown which is at a Connection.PrepareStatement(sql) statement.
Any Suggestions?
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #91409 is a reply to message #91355] |
Thu, 15 August 2002 22:45  |
Praveen
Messages: 57
Registered: November 2001
|
Member |
|
|
The Solution for this kind of problem is
Close result set after particluar operation and even statement after purpose is serverved use finally block if ur useing java where it implicitly closes result set and statement and connection
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #91457 is a reply to message #91355] |
Mon, 16 September 2002 18:53 |
Lane Sharman
Messages: 2
Registered: November 2001
|
Junior Member |
|
|
I believe this to be a hard bug in oracle 9i which has more to do with statistic bookeeping on the part of oracle.
At this time, I am thinking it may be the best strategy to close a j.s.Connection object and see if this results in substracting the metric.
Has anyone seen an oracle report on this???
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #91738 is a reply to message #91355] |
Mon, 27 January 2003 21:11 |
Logan
Messages: 1
Registered: January 2003
|
Junior Member |
|
|
i solved this by using just one Statement for all my code into a loop of DataBase access ( add try and catch sentences)
Statement LoStm= MyConnection.getStatement();
Resultset LoRs=null;
while(whatever)
{
LoRs=LoStm.execute("your SQL")
.
. do something whit data
.
LoStm.close();
LoStm=null;
}
LoStm.close
LoStm=null;
MyConnection.close;
MyConnection=null;
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #92197 is a reply to message #91355] |
Tue, 13 April 2004 05:47 |
Sateesh Gowrisetty
Messages: 1
Registered: April 2004
|
Junior Member |
|
|
Guys,
Please let me know if you resolved this issue. I am facing the same problem. Application crashes after throwing ORA-01000 after running few hours. I close all result set and statements. Any help would be appreciated.
Application is running on SuSe Linux 8
Oracle version is 8.1.7
and the JDK version is 1.4.2_03-b02
-Thanks,
Sateesh
|
|
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #92355 is a reply to message #91355] |
Mon, 14 June 2004 11:39 |
Franklin
Messages: 4
Registered: July 2002
|
Junior Member |
|
|
HEEEEEEELP!!
I have a very similar problem. I've been researching all day and not found anything really useful. I certainly close my resultsets, statements and connections in finally blocks. But this seems to have no effect. I'm using oracle 8i with jdk 1.3.
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #92381 is a reply to message #92377] |
Sun, 20 June 2004 05:57 |
Scott B. Lindgren
Messages: 3
Registered: June 2004
|
Junior Member |
|
|
The Problem is not your code; although, you should make sure you close cursors. The Problem is in the Open cursors that is in the INI file for the Sid. This value is shared by all users, therefore, it could be someone else's code brining it down and not really yours.
Scott
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #92463 is a reply to message #92377] |
Thu, 29 July 2004 14:36 |
j.blackcase
Messages: 2
Registered: July 2004
|
Junior Member |
|
|
One possibility is that you are using two or more "PreparedStatement"s simultaneously - I believe this is legitimate use, but does not work properly in my experience eg the (abbreviated) code below runs 40 times ok, and then fails (ORA-01000) - I changed this to one query (PreparedStatement) and all is fine (tested it 10,000 iterations - with no problem)...
PreparedStatement pstmt = null;
ResultSet rs = null;
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try
{
....
pstmt = connection.prepareStatement("SELECT.......");
rs = pstmt.executeQuery();
while(rs.next())
{
....
pstmt2 = connection.prepareStatement("SELECT......");
rs2 = pstmt2.executeQuery();
...
}
}
finally
{
rs.close();
pstmt.close();
rs2.close();
pstmt2.close();
}
|
|
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #92602 is a reply to message #92463] |
Wed, 06 October 2004 08:55 |
Bhaskar Kolluru
Messages: 1
Registered: October 2004
|
Junior Member |
|
|
In your case the reason why you getting that error is because you are creating the
pstmt2 = connection.prepareStatement("SELECT......");
inside the loop.
Move the pstmt2 = connection.prepareStatement("SELECT......"); outside the loop and that should work without any issues.
Thanks
Bhaskar
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #92635 is a reply to message #92381] |
Mon, 01 November 2004 21:46 |
Anuj
Messages: 9
Registered: August 2002
|
Junior Member |
|
|
You are absolutelty right... This problem is because of this parameter only.... this can be reset in the INI file with "max_cursors=xxxx".
You have to be very sure about the possibility of Open cursors in the session as this is a shared variable for the complete instance.
|
|
|
|
|
|
|
|
| Re: ORA-01000: maximum open cursors exceeded [message #308220 is a reply to message #91355] |
Sat, 22 March 2008 08:37 |
stsaravanan1983
Messages: 1
Registered: March 2008
Location: Chennai
|
Junior Member |
|
|
Hi All,
We faced this problem in our application and resolved it recently.
There are two solutions for this problem.
1. Close all unused cursors(Statement, Resultset or connection) once it is not needed. Also dont create Statement or Prepared Statement Object in the loop. If it is currently inside the loop,place the stmt = con.prepareStatement(query) statement outside the loop. IT will solve the problem(99% guaranteed)
2. Ask your DBA to increase the maximum number of open_cursors value to above 500.(WE have changed it to 1000 ) to resolve this issue.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
