My experience, findings and thoughts in my daily work with Oracle products

How to Cope with an ORA-01000 Error

E-mail this post

Remember me (?)

All personal information that you provide here will be governed by the Privacy Policy of More...

I prepared a document about this issue one week ago and I am posting here some quotes from it.

May be many DBAs have faced with this kind of error but the information that can be found in Internet is very scarce. This error is something like ORA-01555 (snapshot too old) error - there is no usual workaround but there are a lot of suggestions. For the full document, go to the end of my post.


What an Oracle DBA must do when face this kind of error.

Lets first take a look at the documentation:

ORA-01000 maximum open cursors exceeded

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.
This error happens a lot in association with some kind of application.
This error also happens at the database level, with just regular inserts, updates, deletes, etc. in PL/SQL or in SQL*Plus, etc.
The reason you receive this error is because Oracle has reached the set limit for open cursors
allowed for that executable or that user session. There are two kinds of open cursors:
implicit and explicit. Here is some background on how cursors work.

To process a SQL statement, Oracle opens a work area called a private SQL area. This private SQL area stores information needed to execute a SQL statement. Cursors are stored in this area to keep track of information. An IMPLICIT cursor is declared for all data definition and data manipulation statements. These are internal to Oracle. For queries that return more than one row, you must declare an EXPLICIT cursor to retrieve all the information. You can tune explicit cursors more easily as you can decide when to open them and close them.

Implicit cursors are harder to tune because they are internal to Oracle. If the application is tuned carefully, it may cut down the number of implicit cursors opened.

Keep in mind that the maximum number of allowed cursors is per session, not per instance.


There are two ways to workaround this ORA-01000 error. You can tune cursor usage at the database level and at the application level.

Tuning at the DATABASE LEVEL

There is a parameter you can set in the init.ora that determines the number of cursors a user can open in a session: OPEN_CURSORS.

OPEN_CURSORS by default is 50 and usually, this is not high enough. The highest value you can set this parameter to is operating system dependant. To solve the ORA-01000 error, set the OPEN_CURSORS to a higher number. You may need to set it to the maximum of the operating system limit.

In many systems it is set to 1000 (or even more – 3000, 50000) without any problem. But setting of this parameter to values more than 1000 must be discussed, if there is such need, it will means that something is wrong with the application that leads to that error. Then the application code must be revised instead of changing of this parameter to higher values. Even values more than 300 could be considered as bigger.

Consequences to changing this parameter:
This parameter does not affect performance in any way but Oracle will now need a little more memory to store the cursors. It will affect only used memory, not the resources thatOracle will need to support this increased value.


For this level is responsible the development team.

onitoring and detecting of the causing problem

The Oracle DBA must try to identify and localize the problem and if it cannot be solved by himself alone then he/she must report it to the development and provide them with all needed information like: frequency of the error, some regularity in appearing of the error, behavior, results from executed scripts and trace files. This will help the problem to be easily identified and solved if it is coming from the application software.

Using scripts
To be able to resolve the problem, a DBA could use several scripts that will help to him/her in that situation.


Start tracing

For deeper investigation of the problem, you can start tracing the problem session or the instance in order to identify the problem. For more details about these actions, see the point Tracing in Advanced Topics subject bellow.



4. Advanced Topics
4.1. Tracing.
4.2. PL/SQL Cursor Sharing
4.2.1. Simple schema of PL/SQL and session cursor sharing
4.2.2. Oracle and PL/SQL Cursor Sharing
4.2.3. PL/SQL Cached Cursors vs. Session Cached Cursors
4.3. Cursor reuse in PL/SQL static SQL
4.4. Connection pooling in MOD_PLSQL
4.4.1. Introduction
4.4.2 Connection Pooling
4.4.3. Closing Pooled Database Sessions
4.4.4. Connection Pooling and Oracle HTTP Server Configuation
4.4.5. Tuning the Number of Database Sessions


You can download the full document from this URL:

I just want to note that the document is prepared for a PL/SQL web-based application that explains the "MOD_PLSQL connection pooling" and "DAD performance configuration" at the end. You can skip this section if you are not interested in that subject.

I hope that this document can help to DBAs when they face with this specific error. This document doesn't give the "silver bullet" solution but it can helps for easily finding of the root of the problem and resolving it.

0 Responses to “How to Cope with an ORA-01000 Error”

Leave a Reply

      Convert to boldConvert to italicConvert to link


About me

  • » I'm Radoslav Rusinov
  • » From Sofia, Bulgaria
  • » Employer TechnoLogica Ltd.
  • » I am working as a Database Consultant in Sofia, Bulgaria. My main professional interests are in the database area and especially in the Oracle RDBMS, including database design, development, security and administration.
  • » The views expressed on this blog are my own and do not necessarily reflect the views of my employing company and its affiliates
  • » My profile

RSS 2.0 Feed

Search This Blog with Google

Search This Blog with Free Find

powered by FreeFind

Search This Blog with Technorati

Previous Posts


Articles & Presentations

Discover Bulgaria

Oracle News & Blogs Aggregators

Oracle Resources

Remote DBA

Oracle User Groups

Oracle Blogs

Oracle Forums

Security Resources

Professional CV

Blog Statistics



               Page Rank Checker