The application has been fine for days. Then one request fails, then a few more, all from the same app server:

ORA-01000: maximum open cursors exceeded

The usual reaction is to raise OPEN_CURSORS. It’s one ALTER SYSTEM, it needs no restart, and the errors stop. Then they come back a week later.

ORA-01000 is almost never a limit that’s too low. It’s an application that opens cursors and doesn’t close them: a cursor leak. The limit is just where the leak becomes visible. This post covers what the limit counts, how to find the leaking session and the exact statement, the query that makes a healthy session look like the culprit, and why raising the limit doesn’t help the connections that are already failing. It ends with a lab that proves each step on Oracle Database Free 26ai.

What the limit counts

OPEN_CURSORS is the maximum number of cursors one session can have open at once. It is not a database-wide pool. A session at the limit gets ORA-01000; every other session carries on. On 26ai the message reads:

ORA-01000: maximum open cursors for session exceeded

A cursor is the server-side handle for a statement the client is still working with. In application code it’s a Statement or ResultSet in JDBC, a cursor object in Python, a data reader in .NET. It stays open on the server until the client closes it, or the session ends. If the code never closes it, it stays open for as long as the connection lives. With a connection pool, that can be days.

That’s why ORA-01000 shows up late. A code path that leaks one cursor per call is harmless on the first call. Each pooled connection collects its leaked cursors over hundreds or thousands of requests, and fails when it reaches the limit. The request that fails is just the one that needed the next cursor.

The parameter’s documented default is 50. Many databases run with something higher; the container in this post’s lab runs with 300.

What the error tells you on 26ai

On 26ai the first ORA-01000 in a session carries a clue:

ORA-01000: maximum open cursors for session exceeded Potential Leaked SQL_ID:
4cgc7syjwjw3y

In the lab, that SQL_ID was the statement the session held open the most, and not the statement that failed. A session leaked 298 result sets from one procedure and then called a different one. The different call failed, and the message named the leaked statement. In a second test the session leaked 200 result sets from one statement, then 98 from another, and the message still named the one with 200. That’s what you want: the application log now points at the code that leaks, not the innocent request that happened to need the next cursor.

Two things to know:

• Only the first error in the session names it. In the lab the next 21 errors in the same session said only “maximum open cursors for session exceeded”. Find the first one in the application log.
• It may be wrapped. When the failing statement needs internal SQL first (parsing a new statement, for example), the application sees ORA-00604: Error occurred at recursive SQL level 1 with ORA-01000 underneath. The lab got both forms.

Look the SQL_ID up in V$SQL (it stays in the shared pool after the session ends, until it ages out), then find that statement in the code.

The database itself logs nothing. After a run that raised ORA-01000 22 times, the lab found no ORA-01000 in the alert log. If the application swallows its errors, you won’t see this one.

Find the session

If you don’t have the SQL_ID, or want to confirm it, start with the session. The count that the limit applies to is the statistic opened cursors current:

SELECT s.sid, s.serial#, s.username, s.module, s.machine, st.value AS open_cursors
FROM   v$sesstat st
JOIN   v$statname n ON n.statistic# = st.statistic#
JOIN   v$session  s ON s.sid = st.sid
WHERE  n.name = 'opened cursors current'
AND    s.username IS NOT NULL
ORDER  BY st.value DESC
FETCH FIRST 10 ROWS ONLY;

A leaking session sits near OPEN_CURSORS. A healthy one holds a handful. MODULE and MACHINE tell you which application and which app server. Run this on a schedule and you’ll see a leak as a number that grows with uptime long before it reaches the limit.

Find the statement, and don’t count cached cursors

V$OPEN_CURSOR lists cursors per session with their SQL text. It also lists cursors that are closed and kept in the session cursor cache so the next parse of the same statement is cheap. Cached cursors don’t count against OPEN_CURSORS. If you count rows in V$OPEN_CURSOR without filtering, a busy, healthy session can look like the problem.

The lab ran two sessions side by side. One leaked 250 result sets. The other ran 450 different queries four times each, closing every one, with SESSION_CACHED_CURSORS set to 1,000:

session      opened cursors current    rows in V$OPEN_CURSOR
leaky-app    252                       265
busy-app     2                         458

By raw row count, the healthy session has more “open cursors” than the leaking one, and more than the 300 limit. It had two open. 454 of its rows were SESSION CURSOR CACHED. (The lab set the cache to 1,000 to make the point; at the default of 50, expect a few dozen extra rows per session. Some shops set it much higher.)

The CURSOR_TYPE column separates them. Filter on the types that start with OPEN, and group by statement:

SELECT o.sql_id, MAX(o.sql_text) AS sql_text, COUNT(*) AS open_count
FROM   v$open_cursor o
WHERE  o.sid = :sid
AND    o.cursor_type LIKE 'OPEN%'
GROUP  BY o.sql_id
ORDER  BY open_count DESC;

In the lab that returned a single row:

SQL_ID         OPEN_COUNT  SQL_TEXT
4cgc7syjwjw3y  250         SELECT ID, CREATED FROM APP.ORDERS WHERE CUSTOMER_ID = :B1

One statement open 250 times in one session is a leak. Nothing legitimate holds the same query’s result open 250 times at once. If instead you see many different statements, each open a few times, the session may genuinely need more cursors (see the sizing section below).

⤢ Enlarge

Raising OPEN_CURSORS: what it does and doesn’t do

OPEN_CURSORS can be changed with ALTER SYSTEM, without a restart, and in a PDB. The lab raised it from 300 to 400 in the PDB while one session was stuck at the limit:

session                                         result after the raise
stuck at the limit before the raise             55 more calls, 55 x ORA-01000
connected before the raise, leaking after it    first ORA-01000 at call 299 (the old limit)
connected after the raise, same leak            first ORA-01000 at call 399

Two things went wrong with the “fix”:

1. It didn’t reach the connections that were already open. On 26ai in the lab, every session connected before the change kept the old limit, stuck or not. Only new connections got 400. In a pooled application, that means the raise does nothing until the pool reconnects. When the errors stop after a raise, it’s often the app restart that went with it.
2. The leak failed again, just later. The new connection hit ORA-01000 at call 399 instead of 299. The leak rate didn’t change.

So raising the limit is a way to buy time while the code is fixed, and it only buys it after the connections are recycled. Each open cursor also holds memory on the server, so a high limit on a leaking app means more memory held before it fails.

The fix

Right now: the leaked cursors belong to the session. Ending the session releases all of them. Recycle the affected pool’s connections or restart that app server. Capture opened cursors current and the session’s V$OPEN_CURSOR rows first, because they’re gone when the session is.

In the code: close what you open, on every path, including the error path. That means try-with-resources in Java for every Statement, PreparedStatement, CallableStatement and ResultSet, including result sets returned as ref cursors from stored procedures. In Python, use with blocks for cursors; in .NET, using. In PL/SQL, close explicit cursors and DBMS_SQL cursors in the exception handler too. The leak in the lab is a stored procedure that returns a result set to the caller (a SYS_REFCURSOR). The caller owns that cursor, and the caller never closed it. The same 400 calls with each result set read and closed ran with no error, at the original limit of 300.

When the limit really is too low

Some sessions do need more than the default: an application that keeps many statements prepared at once, or a client-side statement cache. Oracle’s JDBC driver, for example, can cache statements per connection, and each cached statement keeps its cursor open on the server. That’s by design, and the cursors are spread over many different statements, not one.

Size OPEN_CURSORS from what the application uses at peak, not from the last error:

SELECT s.module, MAX(st.value) AS peak_open_now
FROM   v$sesstat st
JOIN   v$statname n ON n.statistic# = st.statistic#
JOIN   v$session  s ON s.sid = st.sid
WHERE  n.name = 'opened cursors current'
AND    s.username IS NOT NULL
GROUP  BY s.module
ORDER  BY peak_open_now DESC;

Run it over a normal business day. If the healthy peak is close to the limit, raise it with headroom and recycle the connections. If one module keeps climbing, it’s a leak, and a higher limit only delays the next outage.

Prove it

The lab runs Oracle Database Free in a container. The “application” is SQL*Plus calling a procedure that returns a result set, and holding each result set in its own variable without closing it: the same thing a ResultSet that’s never closed does on the server. Then it breaks and fixes things five ways:

drill   what happens                                             result
leak    320 calls, no result set closed (OPEN_CURSORS 300)       ORA-01000 from call 299; first error names the SQL_ID
blame   298 leaked, then a different call                        the other call fails, message names the leaker;
                                                                 another session runs that call fine
find    a leaking session next to a busy healthy one             raw V$OPEN_CURSOR rows: 265 vs 458;
                                                                 'OPEN%' filter finds the leak, 250 x one SQL
raise   OPEN_CURSORS 300 -> 400 during the outage                stuck and pre-existing sessions keep 300; new: call 399
fix     400 calls, each result set closed                        no ORA-01000

Why call 299 and not 301? The session already had two cursors open before the first call: SQL*Plus’s own statement and one recursive cursor. 298 leaked plus those two is 300.

Don’t take my word for it — run it. The open-cursors lab asserts every row: the failing call number from the limit; that the first error, and only the first, names the leaked statement’s SQL_ID; nothing in the alert log; the leaked statement named even when a different call fails, and a second session unaffected; the healthy session’s V$OPEN_CURSOR row count above the limit while it has almost nothing open; the leaked statement found 250 times by the OPEN% filter; both sessions opened before the raise keeping the old limit and a new one failing at 399; and no error once each result set is closed. If any of that doesn’t reproduce, the run fails. Proven on every CI push.

What teams get wrong

• Raising OPEN_CURSORS as the fix. It moves the failure, and only for new connections. Find the statement first.
• Counting rows in V$OPEN_CURSOR. That includes cached cursors, which are closed. Use opened cursors current, or filter CURSOR_TYPE LIKE 'OPEN%'.
• Blaming the statement that failed. It’s whichever call needed the next cursor. On 26ai, read the SQL_ID in the first error; on any release, look at what the session holds open.
• Closing the ResultSet on the happy path only. An exception between open and close leaks it just the same. Close in finally, or let try-with-resources do it.
• Treating ORA-01000 as a database problem. It’s per session, the database logs nothing, and every other session is fine. The fix is in the application.

Frequently asked questions

What does ORA-01000 maximum open cursors exceeded mean?

It means one database session tried to open more cursors at the same time than the OPEN_CURSORS parameter allows. The limit is per session, not for the whole database, so other sessions keep working. It is almost always caused by a cursor leak: the application opens statements or result sets and doesn't close them, so each connection accumulates open cursors until it reaches the limit. Less often, the application genuinely needs more cursors at once than the limit allows.

How do I find which SQL is causing ORA-01000?

On Oracle Database 26ai the first ORA-01000 in a session includes 'Potential Leaked SQL_ID' followed by the statement the session holds open most; look it up in V$SQL. On any release, find the session with the highest 'opened cursors current' in V$SESSTAT, then query V$OPEN_CURSOR for that SID with CURSOR_TYPE LIKE 'OPEN%', grouped by SQL_ID. One statement open hundreds of times is the leak. In the companion lab that returned one statement open 250 times.

Should I just increase OPEN_CURSORS?

Only if the application genuinely needs more cursors at once, which shows up as many different statements each open a few times. For a leak, raising the limit only delays the error: in the companion lab, raising it from 300 to 400 moved the first ORA-01000 from call 299 to call 399. And the new value applied only to new connections; sessions connected before the change kept the old limit, so a connection pool has to reconnect before a raise has any effect.

Why does V$OPEN_CURSOR show more cursors than OPEN_CURSORS without an error?

Because V$OPEN_CURSOR also lists cursors that are closed and kept in the session cursor cache (CURSOR_TYPE 'SESSION CURSOR CACHED'), and cached cursors don't count against OPEN_CURSORS. In the companion lab a healthy session with SESSION_CACHED_CURSORS set to 1,000 showed 458 rows in V$OPEN_CURSOR against a limit of 300, while its 'opened cursors current' was 2. Filter on CURSOR_TYPE LIKE 'OPEN%' or use the 'opened cursors current' statistic.

Does increasing OPEN_CURSORS require a restart?

No. OPEN_CURSORS can be changed with ALTER SYSTEM while the database is running, and in a PDB. But in the companion lab on 26ai the new value applied only to sessions that connected after the change; sessions that were already connected, including the one stuck at the limit, kept the old limit. Recycle the application's connections after changing it.

How do I fix a cursor leak in Java?

Close every Statement, PreparedStatement, CallableStatement and ResultSet on every path, including when an exception is thrown. try-with-resources does this automatically. Result sets returned from stored procedures as ref cursors are cursors too and must be closed by the caller. To release leaked cursors immediately, recycle the affected connections; ending a session closes all of its cursors.

ORA-01000 is a leak that took days to fill up, reported by whichever request came last. Before you raise anything, find the session by opened cursors current, find the statement by V$OPEN_CURSOR with the cached cursors filtered out, and fix the code that opens it. It’s the same family of problem as the hard-parse storm: application habits showing up as a database error. Then prove it with the open-cursors lab, where one leaked result set per call takes a session down at call 299, and closing it runs 400 calls clean.