Scops
Docs Download

Oracle problems

ORA-01000: Maximum Open Cursors Exceeded. Which Session Leaks, Which Statement, and the Fix

3 November 2026

ORA-01000 reads like a database limit and is nearly always an application leak: one session, one statement, opened in a loop and never closed. The limit only decides on which day the leak is noticed. The session that hits it, the statement it holds open and the number to set are three queries away.

What the error says

ORA-01000: maximum open cursors exceeded. One session has more cursors open than OPEN_CURSORS allows. The limit is per session, not per database: a database with ten thousand cursors open across all sessions is fine, a session with 301 open when the limit is 300 gets the error. The default is 50, the range is 0 to 65535, and the parameter costs nothing until a cursor is actually opened.

Two things look like ORA-01000 and are not:

Which session is near the limit

The statistic that counts is opened cursors current in V$SESSTAT, compared with the parameter:

SELECT s.sid, s.serial#, s.username, s.program, s.machine,
       st.value                               AS open_now,
       ROUND(st.value / p.lim * 100, 1)       AS pct_of_limit
FROM   v$sesstat  st
JOIN   v$statname n ON n.statistic# = st.statistic#
                   AND n.name = 'opened cursors current'
JOIN   v$session  s ON s.sid = st.sid
CROSS  JOIN (SELECT TO_NUMBER(value) AS lim
             FROM   v$parameter
             WHERE  name = 'open_cursors') p
WHERE  s.type = 'USER'
ORDER  BY st.value DESC
FETCH FIRST 15 ROWS ONLY;

A healthy application sits far below the limit in every session. A leak shows as one or a few sessions near the top, all from the same program, and the number grows while the others stay flat.

Which statement it holds open

V$OPEN_CURSOR lists every cursor a session has, with the statement. Group it for the session from the first query:

SELECT oc.sql_id, oc.cursor_type, COUNT(*) AS cursors,
       MIN(SUBSTR(oc.sql_text, 1, 60)) AS sql_text
FROM   v$open_cursor oc
WHERE  oc.sid = :sid
GROUP  BY oc.sql_id, oc.cursor_type
ORDER  BY cursors DESC
FETCH FIRST 10 ROWS ONLY;

The leak is the row with one SQL_ID and hundreds of cursors of type OPEN: the application prepares the same statement again on every iteration and closes none of them. Rows of type SESSION CURSOR CACHED or PL/SQL CURSOR CACHED are Oracle's own caching and not the leak. Without a session number, the same grouping over the whole database names the statement to look for in the code:

SELECT oc.sql_id, COUNT(*) AS cursors,
       COUNT(DISTINCT oc.sid)          AS sessions,
       MIN(SUBSTR(oc.sql_text, 1, 60)) AS sql_text
FROM   v$open_cursor oc
WHERE  oc.cursor_type LIKE 'OPEN%'
GROUP  BY oc.sql_id
ORDER  BY cursors DESC
FETCH FIRST 10 ROWS ONLY;

The fix, in order

  1. Give the application room now. The parameter is dynamic: ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH; (add SID = '*' on RAC). A cursor uses memory only when it is open, so a higher ceiling costs nothing for sessions that never reach it. This stops the paging; it does not stop the leak, which reaches 1000 a few hours later.
  2. Close the leak in the code. The SQL_ID from the second query points at the statement. In Java every PreparedStatement and ResultSet is closed in a finally block or a try-with-resources; in .NET the command and the reader are disposed; in PL/SQL every explicit cursor is closed and every DBMS_SQL cursor gets CLOSE_CURSOR. A driver's statement cache is not a leak: it is bounded by its own size.
  3. Free the session if the application cannot. A session that holds 300 cursors and does nothing keeps them until it disconnects. ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; returns them; the pool opens a clean connection.
  4. Set the number from the data, not from a forum. Rerun the first query at the busiest hour once the leak is closed. The highest session plus half again is the limit; 1000 covers most applications, batch programs with many open result sets need more.

See it coming

A leak is invisible at any single reading: 180 cursors in a session today, 200 tomorrow, each normal on its own. Radar Engine, the detection layer of Scops, reads the shape, and it reads it without an agent on the server:

DetectorClockWhen it fires
Open cursors leakHourly, against the session countOpen cursors rise for days while sessions do not: Watch. Doubled: Warning.
Sessions near the limitHourly level and forecast, against sessions70%: Watch. 80%: Warning. 90%: Critical.
Session surgeLive, against the hour beforeA pool that reconnects after ORA-01000 shows here first.

The finding names the database and the day the leak started, so the deploy that introduced it is one calendar look away, and the session breakdown shows which program carries it.

Questions

Does a higher OPEN_CURSORS cost memory?
No. A cursor takes memory when it is opened, the parameter is only the ceiling. Oracle's own reference says there is no added overhead in setting it higher than a session uses.

Is ORA-01000 a database problem or an application problem?
Almost always the application: one statement, prepared in a loop and never closed. The database limit decides only when the leak is noticed. Raise it to buy time, then fix the code.

Does SESSION_CACHED_CURSORS fix it?
No. It caches the cursors of repeated statements so that they parse faster; it does not close what the application left open.

Why is ORA-01000 not in the alert log?
It is returned to the session that caused it, like any statement error, so it appears in the application's log. The database-side trace is the first query, run before the session disconnects.

Related problems

Sources