ORA-01000: Unclosed Result Sets Took Down the Report Service

ORA-01000 doesn't mean the database is overloaded. It means one session is holding on to cursors it never closed. Usually it's application code that opens a statement or result set and loses track of it on an error path. Because the limit is per session, and connection pools keep sessions alive for hours, the leak builds up quietly. Then one pooled connection crosses the limit and starts failing while the rest look healthy.

This guide shows what actually leaks cursors (and what doesn't), how to find the leaking session and statement in two queries, and why raising OPEN_CURSORS is a delay rather than a fix.

What Actually Counts as a Cursor (It's Not Just SELECT)

Every SQL statement a session runs needs a cursor: SELECT, INSERT, UPDATE, DELETE, and PL/SQL blocks. The OPEN_CURSORS parameter caps how many a single session can have open at once. What leaks and what doesn't matters here. Static SQL inside PL/SQL, and cursor FOR loops, are managed for you. Explicit cursors declared locally in a procedure are closed when the block exits. The things that leak are the ones whose lifetime you control: JDBC Statements and ResultSets, REF CURSORs handed to the application, DBMS_SQL cursors, and cursors declared at package level, which live as long as the session. OPEN_CURSORS defaults to 50, and most database setup templates set it to 300. The limit itself reserves no memory; only cursors that are actually open use any.

-- io.thecodeforge — Database tutorial
-- Check current OPEN_CURSORS setting and per-session usage
SELECT name, value
FROM v$parameter
WHERE name = 'open_cursors';

-- Find sessions with the most open cursors
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       st.value AS open_cursors
FROM v$session s
JOIN v$sesstat st ON st.sid = s.sid
JOIN v$statname sn ON sn.statistic# = st.statistic#
WHERE sn.name = 'opened cursors current'
  AND st.value > 0
ORDER BY st.value DESC;

The Classic Leak: Cursors Closed Only on the Happy Path

In PL/SQL, the cursors that truly leak are the ones that outlive a single call. DBMS_SQL is the classic: DBMS_SQL.OPEN_CURSOR returns a number, and that cursor stays open until you call CLOSE_CURSOR or the session ends. If an exception skips the close, every failing call leaks one cursor, and in a pooled session those add up across requests. Package-level cursors behave the same way, because package state lives for the whole session. Note what does not leak: reopening a local explicit cursor that is still open raises ORA-06511 'cursor already open', and a local cursor is closed automatically when its block exits. The fix is to close in the exception handler too, or to avoid hand-managed cursors entirely.

-- io.thecodeforge — Database tutorial
-- BAD: leaks one DBMS_SQL cursor every time the parse/execute fails
CREATE OR REPLACE FUNCTION count_rows(p_table VARCHAR2) RETURN NUMBER IS
  c   INTEGER;
  n   NUMBER;
  rc  INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table), DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_COLUMN(c, 1, n);
  rc := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE(c, 1, n);
  DBMS_SQL.CLOSE_CURSOR(c);          -- only reached on success
  RETURN n;
END;
/

-- GOOD: close on every path
--   EXCEPTION WHEN OTHERS THEN
--     IF DBMS_SQL.IS_OPEN(c) THEN DBMS_SQL.CLOSE_CURSOR(c); END IF;
--     RAISE;

REF CURSORs: Opened in the Database, Leaked in the App

A procedure that returns a SYS_REFCURSOR opens the cursor and hands responsibility to the caller. The database can't close it; only the client can, by closing the ResultSet and Statement. This is the most common source of ORA-01000 in Java and .NET applications. The code works perfectly in tests, then leaks on the one error path that skips the close. Inside PL/SQL, the same rule applies to cursor variables passed between subprograms. Whoever ends up holding a REF CURSOR must close it. Reopening the same cursor variable with OPEN ... FOR replaces its previous query, so the leak comes from cursors that are handed off and forgotten, not from reopening one variable.

-- io.thecodeforge — Database tutorial
-- The procedure opens the cursor; the CALLER must close it
CREATE OR REPLACE PROCEDURE get_report(p_day DATE, p_rows OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_rows FOR
    SELECT customer_id, total FROM daily_sales WHERE sale_day = p_day;
END;
/

-- PL/SQL caller: fetch, then close on every path
DECLARE
  c   SYS_REFCURSOR;
  id  NUMBER; amt NUMBER;
BEGIN
  get_report(TRUNC(SYSDATE), c);
  LOOP
    FETCH c INTO id, amt;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    IF c%ISOPEN THEN CLOSE c; END IF;
    RAISE;
END;
/

Connection Pools: Why It Looks Random

ORA-01000 is per session, and a connection pool keeps each session alive for hours or days. A leak of one cursor per failed request builds up on whichever pooled connection served that request. Eventually one connection crosses the limit. From then on, every request routed to it fails, and every other connection works. That's why the error looks random and intermittent. The fix is in the code: close Statements and ResultSets in try-with-resources, so they close even when an exception is thrown. For immediate relief, evict or recycle the bad connection. Most pools support a maximum connection lifetime, and keeping it to hours rather than days limits how far a leak can grow before it's noticed.

// io.thecodeforge — Database tutorial
import java.sql.*;

public class CursorLeakExample {
    // BAD: statement never closed — leaks a cursor per call
    public int countPendingInvoices(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM invoices WHERE status = 'PENDING'");
        rs.next();
        return rs.getInt(1);
        // MISSING: rs.close(); stmt.close();
    }

    // GOOD: try-with-resources closes both ResultSet and Statement
    public int countPendingInvoicesFixed(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM invoices WHERE status = 'PENDING'")) {
            rs.next();
            return rs.getInt(1);
        }
    }
}

Diagnosing the Leak: V$OPEN_CURSOR Is Your Friend

When ORA-01000 fires, you need to know what cursors are open and why. V$OPEN_CURSOR shows every open cursor per session, including the SQL text. This is the difference between guessing and knowing.

Run this query against the leaking session (you'll need the SID from V$SESSTAT). You'll see the exact SQL statements that are still open. If you see the same statement repeated 200 times, you've found your leak.

One caveat: V$OPEN_CURSOR only shows cursors that are currently open. If the session already errored out and closed everything, you're too late. Set up a monitoring job that snapshots this view every minute when opened cursors current exceeds a threshold.

-- io.thecodeforge — Database tutorial
-- Step 1: find the leaking SID
SELECT sid, value
FROM v$sesstat st
JOIN v$statname sn ON sn.statistic# = st.statistic#
WHERE sn.name = 'opened cursors current'
ORDER BY value DESC
FETCH FIRST 5 ROWS ONLY;

-- Step 2: see what cursors are open for that SID
SELECT sql_text, COUNT(*) AS times_open
FROM v$open_cursor
WHERE sid = 142
GROUP BY sql_text
ORDER BY times_open DESC;

The Band-Aid: Raising OPEN_CURSORS (And When It's Okay)

Raising OPEN_CURSORS is the fastest way to stop the errors, and the most overused fix. If the app leaks, a higher limit only means the leak runs longer before it fails, and it's harder to diagnose when it does. There are legitimate reasons to raise it. Apps that cache many prepared statements per connection, or ORMs with statement caching, can genuinely need several hundred cursors per session. The limit doesn't reserve memory, so a higher value costs nothing until cursors are actually opened. The real risk of a very high value is losing the early warning, not the memory. The rule: confirm there's no leak (a flat 'opened cursors current' over time), then size the limit with headroom, typically 500 to 1,000.

-- io.thecodeforge — Database tutorial
-- Check the current value
SHOW PARAMETER open_cursors

-- Raise it now and persist it (all RAC instances)
ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH SID = '*';

-- Verify
SHOW PARAMETER open_cursors

-- Note: OPEN_CURSORS is system-level only; ALTER SESSION cannot change it.

The Real Fix: Cursor FOR Loops and Exception-Safe Closing

The permanent fix is simple to state: close every cursor you open, on every path. In PL/SQL, prefer cursor FOR loops for static queries. They can't leak. When you need explicit control (BULK COLLECT with LIMIT, DBMS_SQL, REF CURSORs), close in the normal path and again in the exception handler, guarded by %ISOPEN or DBMS_SQL.IS_OPEN. In application code, try-with-resources (Java), using blocks (.NET) or context managers (Python) do the same job: the close happens however the block exits.

-- io.thecodeforge — Database tutorial
-- Pattern 1: cursor FOR loop — automatically closes, even on exception
BEGIN
  FOR invoice_rec IN (SELECT invoice_id FROM invoices WHERE status = 'PENDING') LOOP
    FOR line_rec IN (SELECT line_id, amount FROM invoice_lines WHERE invoice_id = invoice_rec.invoice_id) LOOP
      -- process line...
      NULL;
    END LOOP;
  END LOOP;
END;
/

-- Pattern 2: explicit cursor with exception-safe close
DECLARE
  CURSOR line_cur IS
    SELECT line_id, amount FROM invoice_lines WHERE invoice_id = :invoice_id;
  v_line_id invoice_lines.line_id%TYPE;
  v_amount  invoice_lines.amount%TYPE;
BEGIN
  OPEN line_cur;
  LOOP
    FETCH line_cur INTO v_line_id, v_amount;
    EXIT WHEN line_cur%NOTFOUND;
    -- process line...
  END LOOP;
  CLOSE line_cur;
EXCEPTION
  WHEN OTHERS THEN
    -- Ensure cursor is closed before propagating the error
    IF line_cur%ISOPEN THEN
      CLOSE line_cur;
    END IF;
    RAISE;
END;
/

When NOT to Use Explicit Cursors at All

Explicit OPEN/FETCH/CLOSE code is often unnecessary. Since Oracle 10g, a cursor FOR loop fetches 100 rows at a time behind the scenes, so it's both shorter and fast. Keep explicit cursors for the cases that need them: BULK COLLECT with a LIMIT for large batches, REF CURSORs passed to other programs, and dynamic SQL through DBMS_SQL. In code review, when you see OPEN ... LOOP ... FETCH ... EXIT WHEN ... CLOSE, ask whether it could be a FOR loop. Usually it can.

-- io.thecodeforge — Database tutorial
-- Legitimate use of explicit cursor: BULK COLLECT with LIMIT
DECLARE
  CURSOR invoice_cur IS
    SELECT invoice_id FROM invoices WHERE status = 'PENDING';
  TYPE invoice_tab IS TABLE OF invoices.invoice_id%TYPE;
  v_invoices invoice_tab;
BEGIN
  OPEN invoice_cur;
  LOOP
    FETCH invoice_cur BULK COLLECT INTO v_invoices LIMIT 100;
    EXIT WHEN v_invoices.COUNT = 0;
    FORALL i IN 1..v_invoices.COUNT
      UPDATE invoices SET status = 'PROCESSED' WHERE invoice_id = v_invoices(i);
    COMMIT;
  END LOOP;
  CLOSE invoice_cur;
EXCEPTION
  WHEN OTHERS THEN
    IF invoice_cur%ISOPEN THEN CLOSE invoice_cur; END IF;
    RAISE;
END;
/