why

Why it happens

Oracle tracks every active cursor (open SQL or PL/SQL statement handle) per session.
You hit the limit when:

Code opens cursors but never closes them (typical in loops or dynamic SQL).

JDBC or middleware keeps prepared statements open.

Application logic repeatedly runs queries in a loop without reusing or freeing cursors.

The parameter OPEN_CURSORS in your database config is too low.

🔍 Step 1: Check current limit

Run this in SQL*Plus or any Oracle client:

SHOW PARAMETER open_cursors;

You’ll get something like:

open_cursors = 300

🧠 Step 2: Find which session leaks cursors

This shows who’s holding too many:

SELECT s.sid, s.serial#, s.username, COUNT()
FROM v$open_cursor c
JOIN v$session s ON c.sid = s.sid
GROUP BY s.sid, s.serial#, s.username
ORDER BY COUNT() DESC;

To see what SQL they are keeping open:

SELECT sql_text
FROM v$open_cursor
WHERE sid = ;

🧰 Step 3: Fix depending on situation
🟩 If it’s your PL/SQL code

Make sure every OPEN cursor has a corresponding CLOSE:

OPEN c1;
FETCH c1 INTO v_row;
CLOSE c1;

For implicit cursors in loops:

FOR r IN (SELECT ... ) LOOP
-- safe: Oracle auto-closes it
END LOOP;

For explicit or dynamic SQL:

FOR rec IN (SELECT ...) LOOP
EXECUTE IMMEDIATE ...; -- this can leak if not carefully reused
END LOOP;

→ Move the EXECUTE IMMEDIATE outside the loop when possible, or use FOR loops instead.

🟦 If it’s JDBC / application side

Ensure ResultSet, Statement, and PreparedStatement objects are closed.

Use connection pools that cache prepared statements instead of reopening them each time.

You can also temporarily raise the limit:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

(Requires DBA privileges.)

🧹 Quick check for current session

To see how many cursors your session is using:

SELECT COUNT(*) FROM v$open_cursor WHERE sid = SYS_CONTEXT('USERENV', 'SID');