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.
-----

Workarounds

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.

Tuning at the APPLICATION LEVEL

For this level is responsible the development team.

Monitoring 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.

----

18

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 9.2.0.5 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:
http://mail.dir.bg/~radoslav.rusinov/Blog/ORA-01000.zip