ORA-01000 Maximum Open Cursors Exceeded - Cursor Management

ORA-01000: Maximum Open Cursors Exceeded

Error Overview

Error Text: ORA-01000: maximum open cursors exceeded

This error occurs when a session tries to open more cursors than allowed by the OPEN_CURSORS parameter. It typically indicates cursor leaks in applications where cursors are opened but not properly closed, or legitimate high cursor usage that exceeds the configured limit.

Understanding Oracle Cursors

Cursor Types

Oracle Cursor Types
├── Explicit Cursors - Declared and managed by developers
├── Implicit Cursors - Automatically created for SQL statements
├── Ref Cursors - Cursor variables that can be passed around
└── Cached Cursors - Reusable cursors stored in session cursor cache

Cursor Lifecycle

Cursor Lifecycle
DECLARE → OPEN → FETCH → CLOSE
    ↓        ↓       ↓       ↓
Allocate → Parse → Execute → Deallocate
Memory    SQL      Query    Memory

Diagnostic Steps

1. Check Current Cursor Usage

-- Check current open cursors by session
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  s.osuser,
  COUNT(*) as open_cursors,
  (SELECT value FROM v$parameter WHERE name = 'open_cursors') as max_cursors,
  ROUND(COUNT(*) / (SELECT value FROM v$parameter WHERE name = 'open_cursors') * 100, 2) as pct_used
FROM v$session s, v$open_cursor oc
WHERE s.sid = oc.sid
  AND s.username IS NOT NULL
GROUP BY s.sid, s.serial#, s.username, s.program, s.machine, s.osuser
ORDER BY open_cursors DESC;

-- Check overall cursor statistics
SELECT
  name,
  value,
  CASE name
    WHEN 'opened cursors cumulative' THEN 'Total cursors opened since startup'
    WHEN 'opened cursors current' THEN 'Currently open cursors (all sessions)'
    WHEN 'session cursor cache hits' THEN 'Cursors found in session cache'
    WHEN 'session cursor cache count' THEN 'Cursors currently in session cache'
  END as description
FROM v$sysstat
WHERE name LIKE '%cursor%'
ORDER BY name;

-- Check cursors by SQL statement
SELECT
  sql_id,
  sql_text,
  COUNT(*) as open_count,
  COUNT(DISTINCT sid) as session_count
FROM v$open_cursor
WHERE sql_text IS NOT NULL
GROUP BY sql_id, sql_text
HAVING COUNT(*) > 1
ORDER BY open_count DESC;

2. Identify Cursor Leaks

-- Find sessions with excessive cursor usage
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.status,
  s.logon_time,
  ROUND((SYSDATE - s.logon_time) * 24, 2) as hours_connected,
  COUNT(oc.sid) as open_cursors,
  ROUND(COUNT(oc.sid) / ((SYSDATE - s.logon_time) * 24 * 60), 2) as cursors_per_minute
FROM v$session s
LEFT JOIN v$open_cursor oc ON s.sid = oc.sid
WHERE s.username IS NOT NULL
GROUP BY s.sid, s.serial#, s.username, s.program, s.status, s.logon_time
HAVING COUNT(oc.sid) > 50  -- Sessions with > 50 open cursors
ORDER BY open_cursors DESC;

-- Check for cursor cache efficiency
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  ss.name as statistic,
  ss.value
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
  AND ss.statistic# = sn.statistic#
  AND sn.name IN (
    'opened cursors cumulative',
    'opened cursors current',
    'session cursor cache hits',
    'session cursor cache count'
  )
  AND s.username IS NOT NULL
  AND ss.value > 0
ORDER BY s.sid, sn.name;

-- Find SQL with high cursor usage patterns
SELECT
  oc.sql_id,
  oc.sql_text,
  COUNT(*) as instances,
  COUNT(DISTINCT oc.sid) as sessions,
  MIN(oc.last_sql_active_time) as first_active,
  MAX(oc.last_sql_active_time) as last_active
FROM v$open_cursor oc
WHERE oc.sql_text IS NOT NULL
GROUP BY oc.sql_id, oc.sql_text
HAVING COUNT(*) > 5
ORDER BY instances DESC;

3. Analyze Session Cursor Cache

-- Check session cursor cache configuration and usage
SELECT
  s.sid,
  s.username,
  s.program,
  sc.value as session_cached_cursors,
  ss1.value as cache_hits,
  ss2.value as cache_count,
  CASE
    WHEN ss1.value > 0 THEN ROUND((ss1.value / (ss1.value + ss2.value)) * 100, 2)
    ELSE 0
  END as cache_hit_ratio
FROM v$session s
LEFT JOIN v$sesstat ss1 ON s.sid = ss1.sid AND ss1.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session cursor cache hits')
LEFT JOIN v$sesstat ss2 ON s.sid = ss2.sid AND ss2.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session cursor cache count')
LEFT JOIN v$parameter sc ON sc.name = 'session_cached_cursors'
WHERE s.username IS NOT NULL
  AND (ss1.value > 0 OR ss2.value > 0)
ORDER BY cache_count DESC, cache_hits DESC;

-- Check cursor cache effectiveness
SELECT
  'session_cached_cursors' as parameter,
  value as current_value,
  CASE
    WHEN value = 0 THEN 'Consider enabling session cursor cache'
    WHEN value < 50 THEN 'Consider increasing to 50-200'
    ELSE 'Current setting appears adequate'
  END as recommendation
FROM v$parameter
WHERE name = 'session_cached_cursors'
UNION ALL
SELECT
  'open_cursors' as parameter,
  value as current_value,
  CASE
    WHEN value < 300 THEN 'Consider increasing for high-cursor applications'
    WHEN value > 1000 THEN 'Very high - check for cursor leaks'
    ELSE 'Current setting appears reasonable'
  END as recommendation
FROM v$parameter
WHERE name = 'open_cursors';

Immediate Solutions

Solution 1: Increase OPEN_CURSORS Parameter

Temporary Increase

-- Check current setting
SHOW PARAMETER open_cursors;

-- Increase limit (can be done dynamically)
ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

-- Check the change took effect
SELECT name, value FROM v$parameter WHERE name = 'open_cursors';

-- Monitor impact
SELECT
  COUNT(*) as total_open_cursors,
  (SELECT value FROM v$parameter WHERE name = 'open_cursors') as max_allowed,
  ROUND(COUNT(*) / (SELECT value FROM v$parameter WHERE name = 'open_cursors') * 100, 2) as pct_used
FROM v$open_cursor;

Optimal Sizing

-- Calculate recommended OPEN_CURSORS value
WITH cursor_stats AS (
  SELECT
    MAX(cursor_count) as max_concurrent,
    AVG(cursor_count) as avg_concurrent,
    COUNT(DISTINCT sid) as active_sessions
  FROM (
    SELECT sid, COUNT(*) as cursor_count
    FROM v$open_cursor
    GROUP BY sid
  )
)
SELECT
  max_concurrent,
  avg_concurrent,
  active_sessions,
  CASE
    WHEN max_concurrent < 300 THEN 300
    WHEN max_concurrent > 1000 THEN ROUND(max_concurrent * 1.2)
    ELSE ROUND(max_concurrent * 1.5)
  END as recommended_open_cursors
FROM cursor_stats;

Solution 2: Kill Sessions with Excessive Cursors

Identify and Terminate Problem Sessions

-- Find sessions that likely have cursor leaks
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  COUNT(oc.sid) as open_cursors,
  'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' as kill_command
FROM v$session s, v$open_cursor oc
WHERE s.sid = oc.sid
  AND s.username IS NOT NULL
GROUP BY s.sid, s.serial#, s.username, s.program, s.machine
HAVING COUNT(oc.sid) > 500  -- Adjust threshold as needed
ORDER BY open_cursors DESC;

-- Kill specific session (execute the generated command)
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- For application sessions, try graceful disconnect first
-- ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

Solution 3: Enable Session Cursor Cache

Configure Cursor Caching

-- Enable session cursor cache to reduce cursor opens/closes
ALTER SYSTEM SET session_cached_cursors = 100 SCOPE=BOTH;

-- Check if cursor caching is helping
SELECT
  'Before Cache' as period,
  SUM(CASE WHEN name = 'opened cursors cumulative' THEN value END) as total_opens,
  SUM(CASE WHEN name = 'session cursor cache hits' THEN value END) as cache_hits
FROM v$sysstat
WHERE name IN ('opened cursors cumulative', 'session cursor cache hits')
UNION ALL
-- Run this after cache has been enabled for a while
SELECT
  'After Cache' as period,
  SUM(CASE WHEN name = 'opened cursors cumulative' THEN value END) as total_opens,
  SUM(CASE WHEN name = 'session cursor cache hits' THEN value END) as cache_hits
FROM v$sysstat
WHERE name IN ('opened cursors cumulative', 'session cursor cache hits');

-- Monitor cache effectiveness over time
SELECT
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') as check_time,
  value as cache_hits
FROM v$sysstat
WHERE name = 'session cursor cache hits';

Long-Term Solutions

1. Application Code Fixes

Proper Cursor Management Patterns

// Java JDBC - BAD: Cursor leak
public List<Employee> getEmployeesBad(Connection conn, String department) {
    List<Employee> employees = new ArrayList<>();
    try {
        PreparedStatement pstmt = conn.prepareStatement(
            "SELECT id, name, salary FROM employees WHERE department = ?");
        pstmt.setString(1, department);
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            employees.add(new Employee(rs.getInt("id"),
                                     rs.getString("name"),
                                     rs.getDouble("salary")));
        }
        // PROBLEM: ResultSet and PreparedStatement not closed!

    } catch (SQLException e) {
        e.printStackTrace();
    }
    return employees;
}

// Java JDBC - GOOD: Proper resource management
public List<Employee> getEmployeesGood(Connection conn, String department) {
    List<Employee> employees = new ArrayList<>();
    String sql = "SELECT id, name, salary FROM employees WHERE department = ?";

    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, department);

        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                employees.add(new Employee(rs.getInt("id"),
                                         rs.getString("name"),
                                         rs.getDouble("salary")));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return employees;
}

PL/SQL Cursor Management

-- BAD: Cursor not explicitly closed
CREATE OR REPLACE PROCEDURE process_employees_bad AS
  CURSOR emp_cursor IS
    SELECT employee_id, salary FROM employees WHERE department_id = 10;

  v_emp_id NUMBER;
  v_salary NUMBER;
BEGIN
  OPEN emp_cursor;

  LOOP
    FETCH emp_cursor INTO v_emp_id, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;

    -- Process employee
    update_salary(v_emp_id, v_salary * 1.1);
  END LOOP;

  -- PROBLEM: Cursor not closed!
  -- CLOSE emp_cursor; -- This line is missing
END;
/

-- GOOD: Proper cursor management
CREATE OR REPLACE PROCEDURE process_employees_good AS
  CURSOR emp_cursor IS
    SELECT employee_id, salary FROM employees WHERE department_id = 10;
BEGIN
  FOR emp_rec IN emp_cursor LOOP
    -- Cursor automatically managed by FOR loop
    update_salary(emp_rec.employee_id, emp_rec.salary * 1.1);
  END LOOP;
  -- Cursor automatically closed
END;
/

-- Alternative with explicit management
CREATE OR REPLACE PROCEDURE process_employees_explicit AS
  CURSOR emp_cursor IS
    SELECT employee_id, salary FROM employees WHERE department_id = 10;

  v_emp_id NUMBER;
  v_salary NUMBER;
BEGIN
  OPEN emp_cursor;

  BEGIN
    LOOP
      FETCH emp_cursor INTO v_emp_id, v_salary;
      EXIT WHEN emp_cursor%NOTFOUND;

      update_salary(v_emp_id, v_salary * 1.1);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      IF emp_cursor%ISOPEN THEN
        CLOSE emp_cursor;
      END IF;
      RAISE;
  END;

  CLOSE emp_cursor;
END;
/

2. Implement Cursor Monitoring

Cursor Usage Tracking

-- Create cursor monitoring table
CREATE TABLE cursor_usage_log (
  log_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  sid NUMBER,
  username VARCHAR2(30),
  program VARCHAR2(100),
  open_cursors NUMBER,
  cached_cursors NUMBER,
  alert_level VARCHAR2(20)
);

-- Cursor monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_cursor_usage AS
  CURSOR cursor_usage IS
    SELECT
      s.sid,
      s.username,
      s.program,
      COUNT(oc.sid) as open_cursors,
      NVL(ss.value, 0) as cached_cursors
    FROM v$session s
    LEFT JOIN v$open_cursor oc ON s.sid = oc.sid
    LEFT JOIN v$sesstat ss ON s.sid = ss.sid
      AND ss.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session cursor cache count')
    WHERE s.username IS NOT NULL
    GROUP BY s.sid, s.username, s.program, ss.value
    HAVING COUNT(oc.sid) > 0;

  v_alert_level VARCHAR2(20);
  v_max_cursors NUMBER;
BEGIN
  -- Get maximum cursor limit
  SELECT value INTO v_max_cursors
  FROM v$parameter WHERE name = 'open_cursors';

  FOR cursor_rec IN cursor_usage LOOP
    -- Determine alert level
    IF cursor_rec.open_cursors > v_max_cursors * 0.8 THEN
      v_alert_level := 'CRITICAL';
    ELSIF cursor_rec.open_cursors > v_max_cursors * 0.6 THEN
      v_alert_level := 'WARNING';
    ELSIF cursor_rec.open_cursors > v_max_cursors * 0.4 THEN
      v_alert_level := 'INFO';
    ELSE
      v_alert_level := 'NORMAL';
    END IF;

    -- Log if above normal
    IF v_alert_level != 'NORMAL' THEN
      INSERT INTO cursor_usage_log (
        sid, username, program, open_cursors, cached_cursors, alert_level
      ) VALUES (
        cursor_rec.sid, cursor_rec.username, cursor_rec.program,
        cursor_rec.open_cursors, cursor_rec.cached_cursors, v_alert_level
      );
    END IF;
  END LOOP;

  COMMIT;
END;
/

-- Schedule cursor monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_CURSOR_USAGE',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_cursor_usage',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=10',
    enabled => TRUE
  );
END;
/

3. Cursor Cache Optimization

Tune Session Cursor Cache

-- Analyze cursor reuse patterns
SELECT
  sql_id,
  COUNT(*) as executions,
  COUNT(DISTINCT sid) as sessions,
  ROUND(COUNT(*) / COUNT(DISTINCT sid), 2) as avg_executions_per_session
FROM v$open_cursor
WHERE sql_text IS NOT NULL
GROUP BY sql_id
HAVING COUNT(*) > 10
ORDER BY avg_executions_per_session DESC;

-- Check cursor cache hit ratio by session
SELECT
  s.sid,
  s.username,
  s.program,
  NVL(hits.value, 0) as cache_hits,
  NVL(opens.value, 0) as total_opens,
  CASE
    WHEN NVL(opens.value, 0) > 0 THEN
      ROUND((NVL(hits.value, 0) / opens.value) * 100, 2)
    ELSE 0
  END as hit_ratio_pct
FROM v$session s
LEFT JOIN v$sesstat hits ON s.sid = hits.sid
  AND hits.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session cursor cache hits')
LEFT JOIN v$sesstat opens ON s.sid = opens.sid
  AND opens.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'opened cursors cumulative')
WHERE s.username IS NOT NULL
  AND NVL(opens.value, 0) > 100  -- Sessions with significant cursor activity
ORDER BY hit_ratio_pct ASC, total_opens DESC;

-- Optimize session cursor cache size
WITH cache_analysis AS (
  SELECT
    AVG(cache_count) as avg_cache_count,
    MAX(cache_count) as max_cache_count,
    COUNT(*) as active_sessions
  FROM (
    SELECT
      s.sid,
      NVL(ss.value, 0) as cache_count
    FROM v$session s
    LEFT JOIN v$sesstat ss ON s.sid = ss.sid
      AND ss.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session cursor cache count')
    WHERE s.username IS NOT NULL
  )
)
SELECT
  avg_cache_count,
  max_cache_count,
  active_sessions,
  CASE
    WHEN max_cache_count > 200 THEN 'Consider increasing session_cached_cursors to ' || CEIL(max_cache_count * 1.2)
    WHEN max_cache_count < 50 THEN 'Current session_cached_cursors setting appears adequate'
    ELSE 'Consider setting session_cached_cursors to ' || CEIL(max_cache_count * 1.5)
  END as recommendation
FROM cache_analysis;

4. Application Design Improvements

Connection Pool Cursor Management

// Connection pool configuration for cursor management
public class CursorAwareConnectionPool {
    private static final int MAX_OPEN_CURSORS = 50; // Per connection

    public void configureCursorSettings(Connection conn) throws SQLException {
        // Set session cursor cache
        try (PreparedStatement pstmt = conn.prepareStatement(
                "ALTER SESSION SET session_cached_cursors = 100")) {
            pstmt.execute();
        }

        // Monitor cursor usage for this connection
        schedulePeriodicCursorCheck(conn);
    }

    private void schedulePeriodicCursorCheck(Connection conn) {
        // Implement periodic cursor count checking
        Timer timer = new Timer();
        timer.scheduleAtFixedRate(new TimerTask() {
            @Override
            public void run() {
                try {
                    int openCursors = getOpenCursorCount(conn);
                    if (openCursors > MAX_OPEN_CURSORS * 0.8) {
                        logger.warn("High cursor usage detected: " + openCursors +
                                   " cursors open on connection");

                        if (openCursors > MAX_OPEN_CURSORS * 0.95) {
                            // Force connection reset
                            resetConnection(conn);
                        }
                    }
                } catch (SQLException e) {
                    logger.error("Error checking cursor count", e);
                }
            }
        }, 0, 60000); // Check every minute
    }

    private int getOpenCursorCount(Connection conn) throws SQLException {
        String sql = "SELECT COUNT(*) FROM v$open_cursor WHERE sid = " +
                    "(SELECT sid FROM v$session WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID'))";

        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        }
    }
}

Batch Processing with Cursor Management

-- Efficient batch processing with cursor reuse
CREATE OR REPLACE PROCEDURE process_large_dataset AS
  CURSOR data_cursor IS
    SELECT id, data_field FROM large_table WHERE processed = 'N';

  TYPE id_array IS TABLE OF large_table.id%TYPE;
  TYPE data_array IS TABLE OF large_table.data_field%TYPE;

  v_ids id_array;
  v_data data_array;

  v_batch_size CONSTANT NUMBER := 1000;
BEGIN
  OPEN data_cursor;

  LOOP
    -- Fetch in batches to limit cursor usage
    FETCH data_cursor BULK COLLECT INTO v_ids, v_data LIMIT v_batch_size;

    EXIT WHEN v_ids.COUNT = 0;

    -- Process batch
    FORALL i IN 1..v_ids.COUNT
      UPDATE large_table
      SET processed = 'Y',
          processed_date = SYSDATE,
          result = process_data(v_data(i))
      WHERE id = v_ids(i);

    -- Commit batch
    COMMIT;

    -- Log progress
    DBMS_OUTPUT.PUT_LINE('Processed ' || v_ids.COUNT || ' records');

  END LOOP;

  CLOSE data_cursor;
END;
/

Related Errors

• ORA-01001 - Invalid cursor
• ORA-01002 - Fetch out of sequence
• ORA-01003 - No statement parsed
• ORA-06508 - PL/SQL: could not find program unit

Quick Reference

Emergency Response Steps

1. ✓ Check current cursor usage by session
2. ✓ Increase OPEN_CURSORS parameter temporarily
3. ✓ Identify sessions with excessive cursor usage
4. ✓ Kill problematic sessions if necessary
5. ✓ Enable session cursor cache
6. ✓ Fix application cursor leaks

Quick Commands

-- Check cursor usage
SELECT COUNT(*) as total_open_cursors,
       (SELECT value FROM v$parameter WHERE name = 'open_cursors') as max_allowed
FROM v$open_cursor;

-- Find sessions with most cursors
SELECT sid, username, COUNT(*) as open_cursors
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY sid, username
ORDER BY open_cursors DESC;

-- Increase cursor limit
ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

-- Enable cursor cache
ALTER SYSTEM SET session_cached_cursors = 100 SCOPE=BOTH;

-- Kill session with cursor leak
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Prevention Guidelines

• Always close cursors - Use try-with-resources or explicit close
• Use cursor caching - Enable session_cached_cursors
• Monitor regularly - Track cursor usage patterns
• Set reasonable limits - Size OPEN_CURSORS appropriately
• Fix application leaks - Review code for proper resource management
• Use bulk operations - Reduce individual cursor operations