cursor

💡 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');

Perfect — that dump is actually quite revealing. The culprit is almost certainly this line in your stack trace:

com.citigroup.get.zcc.osscache.ZExecutionCacheToDbPersistentStore.fillSubItems(ZExecutionCacheToDbPersistentStore.java:3793)

and what’s inside it — particularly the SQL call:

select a.INTERNIDENTITY, c.INTERNINSTID, c.ZEXECID, c.INTERNSEQUENCE, ...
from ZExecution a, ZExecTempContraBkrsGtp c
where a.ZEXECID = c.ZEXECID
and a.INTERNVERSION = c.INTERNVERSION
and a.INTERNINSTID = c.INTERNINSTID
and a.INTERNIDENTITY in (....)
order by a.INTERNIDENTITY, c.INTERNSEQUENCE

🔍 Diagnosis

From the trace:

The error happens inside fillSubItems(), called by recover().

This method probably runs inside a loop — once per “main record” — and creates a new PreparedStatement each time, without closing the previous one.

Something like this pseudocode:

for (Execution exec : executions) {
PreparedStatement ps = conn.prepareStatement(SQL);
ps.setInt(1, exec.getId());
ResultSet rs = ps.executeQuery();
// process rs...
// missing rs.close();
// missing ps.close();
}

This exact pattern causes open cursor buildup — each unclosed statement = one cursor left open in Oracle.

💣 Root cause summary

Method involved:
ZExecutionCacheToDbPersistentStore.fillSubItems()

Problem:
PreparedStatement and/or ResultSet not closed after use, likely inside recovery loop.

Stack pattern:
recover() → fillSubItems() → repeated selects → ORA-01000

✅ Fix options
1️⃣ Code fix (preferred)

In fillSubItems():

PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(SQL);
// set params
rs = ps.executeQuery();
...
} finally {
if (rs != null) try { rs.close(); } catch (SQLException ignored) {}
if (ps != null) try { ps.close(); } catch (SQLException ignored) {}
}

If you’re using batching or many IDs in one query, reuse the statement:

PreparedStatement ps = conn.prepareStatement(SQL);
for (Execution exec : executions) {
ps.setInt(1, exec.getId());
try (ResultSet rs = ps.executeQuery()) {
...
}
}
ps.close();

2️⃣ Database fix (temporary workaround)

If you can’t modify code immediately:

ALTER SYSTEM SET open_cursors = 2000 SCOPE=BOTH;

Or session-specific:

ALTER SESSION SET open_cursors = 2000;

But that’s just a band-aid — the code leak remains.

3️⃣ Monitoring check

You can verify leaks like this:

SELECT sid, COUNT(*)
FROM v$open_cursor
GROUP BY sid
ORDER BY 2 DESC;

and then:

SELECT sql_text
FROM v$open_cursor
WHERE sid = ;

You’ll likely see dozens/hundreds of identical “select a.INTERNIDENTITY, c.INTERNINSTID...” statements piling up.