Home » Developer & Programmer » JDeveloper, Java & XML » ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded [message #91355] Fri, 26 July 2002 02:45 Go to next message
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 #91359 is a reply to message #91355] Fri, 26 July 2002 12:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
this should be dealt from the application only.
It has nothing to do with database.
some of statements are NOT CLOSED properly.
even if open_cursors=10000, this error will occur.
before opening every statement, check whether it is already left Open or not.
similarly try to close every statement.
Re: ORA-01000: maximum open cursors exceeded [message #91409 is a reply to message #91355] Thu, 15 August 2002 22:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #91505 is a reply to message #91355] Sat, 12 October 2002 10:35 Go to previous messageGo to next message
ananth
Messages: 8
Registered: October 2002
Junior Member
Hi all,
i am also facing the same problem, even after closing the result set and statements, if u get any solution plz share with me.
Re: ORA-01000: maximum open cursors exceeded [message #91738 is a reply to message #91355] Mon, 27 January 2003 21:11 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #92233 is a reply to message #91355] Thu, 29 April 2004 10:36 Go to previous messageGo to next message
aayush verma
Messages: 1
Registered: April 2004
Junior Member
I am also facing the same problem. i have made sure that all the cursors are closed.
so if there is a resolution on this then update me also.
Thanks,
Aayush
Re: ORA-01000: maximum open cursors exceeded [message #92245 is a reply to message #91355] Tue, 04 May 2004 10:21 Go to previous messageGo to next message
lakshmi
Messages: 22
Registered: July 2000
Junior Member
Hello all,

I am facing the same problem, even though I made sure all the reslut sets, statements are closed.

Anymore suggestions...?

Thanks in advance.
Lakshmi.
Re: ORA-01000: maximum open cursors exceeded [message #92355 is a reply to message #91355] Mon, 14 June 2004 11:39 Go to previous messageGo to next message
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 #92377 is a reply to message #91355] Fri, 18 June 2004 12:31 Go to previous messageGo to next message
Santhosh
Messages: 24
Registered: July 2002
Junior Member
I am getting frustated with this error. I changed my code 4 to 5 times but still no use. I am running 8.0.5 (i know it's old) and have cursor close commands almost every single line of the code but no help.

Any ideas please.
Re: ORA-01000: maximum open cursors exceeded [message #92381 is a reply to message #92377] Sun, 20 June 2004 05:57 Go to previous messageGo to next message
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 SOLUTION [message #92448 is a reply to message #91409] Wed, 21 July 2004 09:57 Go to previous messageGo to next message
Reuben
Messages: 1
Registered: July 2004
Junior Member
Praveen is correct. I explicitly closed ResultSets and Statements as soon as I was done with them, and now I can iterate through a large number of queries.
Re: ORA-01000: maximum open cursors exceeded [message #92463 is a reply to message #92377] Thu, 29 July 2004 14:36 Go to previous messageGo to next message
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 #92485 is a reply to message #91409] Wed, 11 August 2004 04:38 Go to previous messageGo to next message
Bob Hentges
Messages: 1
Registered: August 2004
Junior Member
I ran into the same problem (having multiple Preparedstatements
running one aside the other). Closing them as soon as possible, did
help a great lot ;-)
Re: ORA-01000: maximum open cursors exceeded [message #92490 is a reply to message #91355] Thu, 12 August 2004 09:30 Go to previous messageGo to next message
Allen Kenney
Messages: 1
Registered: August 2004
Junior Member
Try closing the Statement after each insert/update:

ResultSet resultSet = executeQuery(query, conn);
Statement stmt = resultSet.getStatement();
resultSet.close();
stmt.close();
Re: ORA-01000: maximum open cursors exceeded [message #92602 is a reply to message #92463] Wed, 06 October 2004 08:55 Go to previous messageGo to next message
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 SOLUTION [message #92609 is a reply to message #92448] Sun, 10 October 2004 21:06 Go to previous messageGo to next message
ashok
Messages: 32
Registered: July 2000
Member
what is the solution for this problem , How can I clear the problem
Re: ORA-01000: maximum open cursors exceeded [message #92635 is a reply to message #92381] Mon, 01 November 2004 21:46 Go to previous messageGo to next message
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 SOLUTION [message #92837 is a reply to message #92609] Thu, 17 February 2005 22:13 Go to previous messageGo to next message
SIVA PRASAD G
Messages: 1
Registered: February 2005
Junior Member
When I am executing the query inside a loop i m getting this error. each time after executing the query i set the statment and resultset to null.

oracle 9, windows in java
Re: ORA-01000: maximum open cursors exceeded SOLUTION [message #108847 is a reply to message #92837] Sat, 19 February 2005 02:40 Go to previous messageGo to next message
indranil
Messages: 2
Registered: July 2003
Junior Member
try this

CLOSE_CACHED_OPEN_CURSORS = TRUE
RELEASE_CURSOR=YES

write this in init.ora file
Re: ORA-01000: maximum open cursors exceeded [message #308220 is a reply to message #91355] Sat, 22 March 2008 08:37 Go to previous messageGo to next message
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.
Re: ORA-01000: maximum open cursors exceeded [message #308231 is a reply to message #308220] Sat, 22 March 2008 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many topics on this subject.
I don't think it is wise and useful to reopen one that has not been updated since more than 3 years.
Just wait a little bit and you will see the same question asked again.

Regards
Michel

[Updated on: Sat, 22 March 2008 09:51]

Report message to a moderator

icon14.gif  Re: ORA-01000: maximum open cursors exceeded [message #476794 is a reply to message #308231] Sun, 26 September 2010 03:19 Go to previous messageGo to next message
nhassona
Messages: 1
Registered: September 2010
Junior Member
Hi All,

I have the same problem in my appplication and it solved by running this command on the oracle prompt:
ALTER SYSTEM SET open_cursors=1500 scope=both;

[Updated on: Sun, 26 September 2010 03:25]

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #476799 is a reply to message #476794] Sun, 26 September 2010 03:39 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And it is a BAD solution.
The correct one is to fix the application to make it close the no more cursors.

Regards
Michel
Previous Topic: Separate Characters in SQL
Next Topic: XSL template
Goto Forum:
  


Current Time: Thu Mar 28 13:22:38 CDT 2024