Home » RDBMS Server » Server Administration » ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...?
ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #264043] Fri, 31 August 2007 09:59 Go to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi,
i am using Oracle9i database where the database integrated with JAVA front end application. now i am getting "ORA-01000: maximum open cursors exceeded " error while open the java front end screen.

How can i resovle the this problem in my database?
Any one can help me.

Regards,
Siva.P
Bangalore
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #264048 is a reply to message #264043] Fri, 31 August 2007 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>How can i resovle the this problem in my database?
By changing the JAVA so that Java code actually explicitly closes the cursors it opens.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #264051 is a reply to message #264043] Fri, 31 August 2007 10:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hello,

Whenever you open a Java ResultSet, Oracle openes a cursor, so close the ResultSet after you are done with it.

You can check the maximum number of cursors a session is allowed to open with a dba user :

SELECT * FROM v$parameter WHERE NAME = 'open_cursors';


and check the cursors that are currently open with :

SELECT * FROM v$open_cursor

Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266403 is a reply to message #264043] Mon, 10 September 2007 14:36 Go to previous messageGo to next message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
increase the "open_cursors" value in the init.ora file
--
Sanjay B.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266548 is a reply to message #264043] Tue, 11 September 2007 02:10 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi Sanjay,

Thanks for your reply, i tried to increase the open cursor value in initora File, The Database designer set the open cursor value is 1000 for my database. But my database has reached the maximum value(1000).So, we are not able to increase the open cursor value against the default value.

Can you tell me the other solution for this issue...? it will be help full for me.

Thanks in Advance...

Thanks,
Siva.P
Bangalore
India.

[Updated on: Tue, 11 September 2007 02:12]

Report message to a moderator

Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266674 is a reply to message #264043] Tue, 11 September 2007 06:50 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Have you tried the other suggestions about explicitly closing your result set(s) within the Java code ?
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266687 is a reply to message #266674] Tue, 11 September 2007 07:21 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Yes, i am checking in Java code as well as database code whether the cursor properly closed or not.

i have complete almost all the package and procedure as well as Java code also. the all the code has properly open and close the cursor properly.
So, what is the other option to do fix the issue?

Tell me the other option to fix the issue.

Thanks,
Siva.P
Bangalore


[Updated on: Tue, 11 September 2007 07:22]

Report message to a moderator

Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266695 is a reply to message #266687] Tue, 11 September 2007 07:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are no other options.

Look at the open cursors and find the ones that are not closed right, and close them in the application code.

The select

SELECT SQL_TEXT, Count(*) FROM v$open_cursor 
 GROUP BY sql_text
 ORDER BY Count(*) DESC;


Should give you the open cursors by SQL in descending order, so you should be able to find the cursor(s) that are the most problematic.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266725 is a reply to message #266695] Tue, 11 September 2007 08:54 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi Thomas G,

i ran the query which you sent, its giving the Sql query and no of open cursor at the moment.

for example SQL Text from the sql query output

SQL_TEXT:
UPDATE FCMT_TRADESPHERE_GW20 SET import_status = 'C',

This is the Query in my Database procedure

UPDATE FCMT_TRADESPHERE_GW20 SET import_status = 'C', update_date = SYSDATE, updated_by = lv_updatedby WHERE gw20_trans_id = i.gw20_trans_id;

Where i.gw20_trans_id is the cursor variable which is declared in begin of the procedure, it is the main cursor using all PL/SQL block in the procedure

So How can i fix the issue...?

See the oupt put file as an attachement.

Thanks,
Siva.P
Bangalore
India.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266751 is a reply to message #266725] Tue, 11 September 2007 09:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If the procedure is called from Java close the prepared statement after it is done. ( Preferably in an "finally" block after the try/catch that does execute the statement )

But the cursor is only open 13 times, which might be OK if about that number of people are working with that part of the application at that time.

How many cursors are open at the moment per session? Are you sure "open_cursors" is set to 1000?



Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266758 is a reply to message #266751] Tue, 11 September 2007 10:03 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Yes Thomas G,

In my application number of people are working in JAVA side and DB side also, My Database has set the open cursor value is 1000

open_cursors integer 1000
open_links integer 4
open_links_per_instance integer 4
read_only_open_delayed boolean FALSE
session_max_open_files integer 10


select * from v$sysstat where name = 'opened cursors current';

see the attached file for current open cursor(its not constant value...) its varying time by time depends upon the user.

Thanks,
Siva.P
Bangalore.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266764 is a reply to message #266758] Tue, 11 September 2007 10:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Please post the formatted output of the following :

SELECT * FROM (
  SELECT sid, Count(*) FROM v$open_cursor 
   GROUP BY sid
   ORDER BY Count(*) DESC)
WHERE ROWNUM < 20;

SELECT NAME, value  FROM v$parameter WHERE NAME = 'open_cursors';
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266769 is a reply to message #266764] Tue, 11 September 2007 10:40 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
ThomasG,

Find the attached file is the output for the first query which you sent.


Output for the second query is

NAME VALUE
====== ========
open_cursors 1000



Thanks,
Siva.P
Bangalore
India.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266771 is a reply to message #266769] Tue, 11 September 2007 10:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK, ~50 open cursors for a session look pretty normal.

So I guess there is some specific place in the application where a cursor is opened (maybe even multiple times inside a loop) and not closed. You will have to find that specific place.

You can either run the query that lists the open cursors per SID now and then, and when the cursor count rises abnormally investigate the open cursors for that SID.

Or you wait until the error happens again, and investigate that specific session then.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266839 is a reply to message #266771] Tue, 11 September 2007 14:34 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
remember - open_cursors is an instance wide setting - not a per session limit. I also think the code is the likely problem. Don't fall into the trap of just increasing the value until the problem goes away.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #267010 is a reply to message #266839] Wed, 12 September 2007 04:33 Go to previous message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Thanks ThomasG and Andrew...
your hinds are very helpful to me...

Thanks,
Siva.P
Bangalore
Previous Topic: configure perfstat
Next Topic: LRU List
Goto Forum:
  


Current Time: Sat Dec 03 07:59:54 CST 2016

Total time taken to generate the page: 0.09885 seconds