ORA-01000

ORA-0100: Maximum open cursors exceeded

What causes this error?

An ORA-01000 error occurs when you open too many cursors.

How to fix it

The initialization parameter OPEN_CURSORS in INITSID.ORA/ SPFILE determines the maximum number of cursors per session.

Check the parameter specified by executing the following SQL:

select * from v$parameter
where name = 'open_cursors'
/

If you want more cursors to be opened at the same time, shut the database, change INITSID.ORA or SPFILE and restart the database, or just execute:

alter system set open_cursors=500 scope=both
/

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 and opened 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
/

The above SQL will tell you about cursors opened at some point of time, but does not tell you about currently open cursors. However it will help you 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 currently by your own session:

select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
/

The following SQL will tell you the number of currently open sessions and the SID of each session currently connected to your database:

select sid, value 
from v$sesstat sest
where statistic# = 3
order by value desc
/

The closing of the cursor change based on the language you use:

• In JDBC: <statement>.close()
• In Pro*C: EXEC SQL CLOSE <cursor>
• In OCI: OCIHandleFree(<statement handle>, OCI_HTYPE_STMT)

These statements will make sure you close every explicitly opened cursor.