Introduction Oracle ORA-01000 `maximum open cursors exceeded` occurs when a database session opens more cursors than the `OPEN_CURSORS` limit allows. This is typically caused by cursor leaks in application code where prepared statements or result sets are not properly closed, and can eventually bring an application to a halt.

Symptoms - `ORA-01000: maximum open cursors exceeded` in application logs - Error occurs after the application has been running for some time - Restarting the application temporarily resolves the issue - `V$OPEN_CURSOR` shows hundreds of cursors per session - Different database drivers report: "Exhausted Resultset" or "Invalid column index"

Common Causes - ResultSet or PreparedStatement not closed in a finally block - Connection pool leaking cursors across borrowed connections - `OPEN_CURSORS` set too low for the application's cursor usage pattern - ORM framework not releasing cursors after query execution - PL/SQL code opening cursors in loops without closing them

Step-by-Step Fix 1. **Identify sessions with the most open cursors": ```sql SELECT s.sid, s.serial#, s.username, s.machine, s.program, COUNT(*) AS open_cursors FROM v$open_cursor oc JOIN v$session s ON oc.sid = s.sid GROUP BY s.sid, s.serial#, s.username, s.machine, s.program ORDER BY open_cursors DESC FETCH FIRST 10 ROWS ONLY; ```

  1. 1.**Find the SQL text of open cursors":
  2. 2.```sql
  3. 3.SELECT
  4. 4.s.sid,
  5. 5.s.serial#,
  6. 6.oc.sql_text,
  7. 7.COUNT(*) AS cursor_count
  8. 8.FROM v$open_cursor oc
  9. 9.JOIN v$session s ON oc.sid = s.sid
  10. 10.WHERE s.sid = 123 -- Replace with the problematic SID
  11. 11.GROUP BY s.sid, s.serial#, oc.sql_text
  12. 12.ORDER BY cursor_count DESC;
  13. 13.`
  14. 14.**Increase OPEN_CURSORS if legitimately needed":
  15. 15.```sql
  16. 16.-- Check current setting
  17. 17.SHOW PARAMETER open_cursors;

-- Increase ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH; ```

  1. 1.**Kill the session with leaked cursors":
  2. 2.```sql
  3. 3.ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;
  4. 4.`
  5. 5.**Fix the application code to properly close cursors":
  6. 6.```java
  7. 7.// BAD: cursor leak
  8. 8.// PreparedStatement ps = conn.prepareStatement(sql);
  9. 9.// ResultSet rs = ps.executeQuery();
  10. 10.// return rs; // rs never closed!

// GOOD: try-with-resources try (PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) { while (rs.next()) { // process } } // Automatically closes both ps and rs ```

Prevention - Use try-with-resources (Java) or context managers (Python) for all cursors - Monitor open cursor count per session with alerting at 80% of limit - Set `OPEN_CURSORS` appropriately (500-1000 for most applications) - Use connection pool validation to detect cursor leaks - Implement cursor count monitoring in application metrics - Review ORM query patterns for cursor management - Set `session_cached_cursors` to reduce parsing overhead without leaking