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.
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.
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
Tuning at the APPLICATION LEVEL
For this level is responsible the development team.
