We help IT Professionals succeed at work.

ORA-01000: maximum open cursors exceeded error msg

npanarella
npanarella asked
on
we are running a JDBC application and keep on getting this error: ORA-01000: maximum open cursors exceeded

We are not sure why we are getting it. We have noticed that it occurs after we have executed several sql statements. has anyone seen this. we are wondering if with JDBC you have to close statement clauses and resultset. we are currently only closing statements. does this ring a bell with anyone..??
Comment
Watch Question

Commented:
Hi,
You must increase maximum open cursors in init.ora
1)shut down database.
2)edit init.ora
3)add at the end of it  :
   open_cursors     = 512  (512 is recommended)
4)save and start up the database

Replay me telling me what hapened.
Best Regards
Mohamed Sabry
Oracle DBA
   
CERTIFIED EXPERT
Top Expert 2008

Commented:
You have to increase your Exception Handling (whatever language you use). A common feature of Oracle is that very often if the DML statement return an error (or trigger on the oracle DB, or PL/SQL code) the error is masked, but the cursor (sometimes implicit) stays open. This is the hidden cause of such kinds of errors. Very often you can increase the init.ora parameter open_cursors to bilions, but the error stays. It is most likely that problems with failing DML statements causes this error.

So study what are the programming tools for closing cursors, quiting DML operation, etc. in your environment and try to catch every possible exception in your program. Obligatory close your DB connection by normal or abnormal end of your program.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Also if you use PL/SQL or other language which gives the possibility to use explicit cursors instead implicit then try to use only explicit cursors.

If it is possible try to embed your DML in triggers or other kind of PL/SQL code and use there explicit cursors instead of implicit cursors. Be ware that every SELECT statement issues implicit cursor and very often they stay inclosed for unknown reason.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Hi npanarella@idg,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. If there is no objection or further activity, I will suggest to:

    Split points between: mohsabry and schwertner

npanarella@idg, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will followup.

EXPERTS: Your input for closing recommendations are REQUESTED.
==========
DanRollins -- EE database cleanup volunteer
Force accepted

** Mindphaser - Community Support Moderator **

schwertner, there will be a separate question with points for your help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.