This content has been marked as final. Show 17 replies
The vast majority of times, 99.9% I would say, the "maximum open cursors exceeded" error is caused by reference cursor leakage in the client (usually Java) application.
Ref cursors in Oracle PL/SQL are "client cursors". I.e. PL/SQL constructs the ref cursor on behalf of a client. The ref cursor handle (pointer) is passed to the client. The client fetches rows from the cursor handle.
Oracle has not idea when the client is indeed done with that ref cursor. That ref cursor must explicitly be closed by the client.
For some reason, this is usually not the case when dealing with Java-based clients that seem to forget this.
As the application does not close its ref cursors, the number of open cursors quickly accumulate and one runs into an ORA-01000 error.
An application (Oracle session) should normally one have a single cursor open. It is very seldom that an application will be processing two cursors at the same time (as this can most times be vastly better done using a server-side JOIN).
> have u restarted the database after changing the
restart is required for the new effect to take place.
It is a Very Bad Idea (tm) to increase the open cursors parameter for this error. It is an attempt to fix the symptoms and not the problem.
It simply moves the Brick Wall a few metres further, allowing one to run even faster into it.
> Means the client is opening the cursors and is not
closing after that.
> Does this mean the earlier opened cursors will not be
used for the current application.
Not really. Some o-o speak to explain a cursor. A cursor in an instantiation of a SQL in the Shared Pool. So the SQL is hard parsed. Execution plan determined. Stored in the Shared Pool.
A cursor is simply an "instatiation" of that SQL.
Typically a client should use a cursor (aka SQL statement handle in many APIs) once. E.g.
The SQL (cursor) handle is created once and re-used in this example. However the actual INSERT SQL now resides in the Shared Pool and can be re-used by any other database session without those sessions having to pay the "expensive price" for a hard parse of the INSERT statement. These sessions will use soft parses and simply re-use that Sharable SQL.
dbHandle = CreateDBSession( ... );
sqlHandle = ParseSQL( dbHandle, 'INSERT INTO FOO VALUES ( :1, :2 )' );
.. some processing...
BindSQL( sqlHandle, 1, var1 );
BindSQL( sqlHandle, 2, var2 );
ExecSQL( sqlHandle );
CloseSQL( sqlHandle );
Cursor leakage occurs when the same variable (e.g. sqlHandle) above is re-used to create new SQL statements without closing that handle first.
In my experience, in Java (and to a lessor extent in C++), developers code PL/SQL procs to create the actual cursor. This is a good thing as it abstracts the SQL from the application and allows SQL tuning (and mods) without touching the client app code.
These SQL procs then return reference cursors. A ref cursor (within the PL/SQL context) has no local scope. It exists globally per session. Explicit PL/SQL cursors for example are auto closed when they become out-of-scope.
Ref cursors are 'client cursors' - cursor handles that are intended to be passed to a client process and processed by that client. Thus these cursor handles never go out-of-scope ito PL/SQL code or even client code. They exist within the session as a global session memory struct.
The client call (or a call to a PL/SQL proc) must be made to explicitly close the ref cursor.
Could you please tell how to check the following
How to check whether any explicit cursor is openingThere is no any tool to figure out the aforesaid staments ,you will have to check it by
within other explicit cursor?
also how to check whether curosor is closed or not?
reading yours own code or just paste the code here which will helpful to others to
find out whats the exact problem.
What you can do on the Oracle side is identify the session that is potentially leaking cursors and the cursor statements. I've found that this information suffices to track down the problem in the client code.
The following SQL looks at the virtual view called V$OPEN_CURSOR. It lists sessions that have multiple cursors opened for the same SQL statement.
Once you have the SQL/cursor address, you can find the SQL statement. The SID (Session ID) can be used to find the details of the client session in the V$SESSION table.
COUNT(c.saddr) as "Cursor Copies"
from v$open_cursor c
COUNT(c.saddr) > 2