ORA-01000: maximum open cursors exceeded

ORA-01000: maximum open cursors exceeded
Cause:
You tried to open too many cursors at once. Each user is restricted to the number of cursors open based on the OPEN_CURSORS parameter in the initialization file.
Action:
The options to resolve this Oracle error are:
Try to close cursors that you are no longer using before opening new cursors.
You may need to shut down Oracle, increase the OPEN_CURSORS parameter in the initialization file, and restart Oracle.
OR
ALTER SYSTEM SET open_cursors = 500 SCOPE=BOTH;
Some other workaround during troubleshooting above error:
The cursors that are counted for this are those explicit cursors that you opened and never closed or the cursors the PL/SQL keeps open. If you use a lot of stored procedures, then you will see lot of cached cursors. From release 8.1, PL/SQL will close these cached cursors on commit.
You can find the list of open cursors and the users who opened them by executing the following SQL
select user_name, status, osuser, machine, a.sql_text
from v$session b,
v$open_cursor a
where a.sid = b.sid;
But the above SQL will tell you about cursors opened at some point of time, but does tell you about currently open cursors. But the above SQL will helps us to track cursor leaks, which would need fixing, to avoid this error in the future.
The SQL given below will tell you how many are open truly:
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
The closing of the cursor change based on the tool you use:
In JDBC, preparedStatement.close() closes the cursor.
In Pro*C, EXEC SQL CLOSE ; does it.
In OCI, there is an API call to close a statement
These statements will make sure you close every explicitly opened cursor.
You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply