【Oracle】ORA-01000: 最大オープン・カーソル数を超えました の原因と解決方法完全ガイド｜PL/SQL・JDBC・診断SQLまで解説

-- 現在の設定値を確認
SELECT name, value
FROM v$parameter
WHERE name = 'open_cursors';

-- セッションごとのオープン・カーソル数を確認
SELECT ses.username, ses.sid, ses.serial#, sta.value AS open_cursors
FROM v$sesstat sta
JOIN v$statname stn ON sta.statistic# = stn.statistic#
JOIN v$session ses ON sta.sid = ses.sid
WHERE stn.name = 'opened cursors current'
  AND ses.username IS NOT NULL
ORDER BY sta.value DESC;

-- セッション別のオープン・カーソル数TOP10
SELECT
    oc.sid,
    s.username,
    s.program,
    COUNT(*) AS open_cursor_count
FROM v$open_cursor oc
JOIN v$session s ON oc.sid = s.sid
WHERE s.username IS NOT NULL
GROUP BY oc.sid, s.username, s.program
ORDER BY open_cursor_count DESC
FETCH FIRST 10 ROWS ONLY;

-- SID=123 のセッションが保持しているカーソルと SQL テキスト
SELECT
    oc.sid,
    oc.cursor#,
    oc.sql_text
FROM v$open_cursor oc
WHERE oc.sid = 123
ORDER BY oc.cursor#;

-- 10秒おきに実行してカーソル数が増え続けていないか確認
SELECT
    TO_CHAR(SYSDATE, 'HH24:MI:SS') AS check_time,
    ses.sid,
    ses.username,
    sta.value AS open_cursors
FROM v$sesstat sta
JOIN v$statname stn ON sta.statistic# = stn.statistic#
JOIN v$session ses ON sta.sid = ses.sid
WHERE stn.name = 'opened cursors current'
  AND ses.username IS NOT NULL
  AND sta.value > 10
ORDER BY sta.value DESC;

DECLARE
    CURSOR c_emp IS SELECT employee_id FROM employees;
    v_id employees.employee_id%TYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_id;
        EXIT WHEN c_emp%NOTFOUND;
        -- ここで例外が発生すると CLOSE が実行されない
        process_employee(v_id);
    END LOOP;
    CLOSE c_emp;  -- 例外発生時はスキップされる
END;

DECLARE
    CURSOR c_emp IS SELECT employee_id FROM employees;
    v_id employees.employee_id%TYPE;
BEGIN
    OPEN c_emp;
    BEGIN
        LOOP
            FETCH c_emp INTO v_id;
            EXIT WHEN c_emp%NOTFOUND;
            process_employee(v_id);
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            -- 例外が発生しても必ず CLOSE する
            IF c_emp%ISOPEN THEN
                CLOSE c_emp;
            END IF;
            RAISE;
    END;
    CLOSE c_emp;
END;

DECLARE
    CURSOR c_dept IS SELECT dept_id FROM departments;
    CURSOR c_emp(p_dept_id NUMBER) IS
        SELECT employee_id FROM employees WHERE dept_id = p_dept_id;
    v_dept departments.dept_id%TYPE;
    v_emp  employees.employee_id%TYPE;
BEGIN
    OPEN c_dept;
    LOOP
        FETCH c_dept INTO v_dept;
        EXIT WHEN c_dept%NOTFOUND;
        -- c_emp を OPEN するが CLOSE していない
        OPEN c_emp(v_dept);
        FETCH c_emp INTO v_emp;
        -- 処理が途中で終わっても c_emp がオープンのまま残る
    END LOOP;
    CLOSE c_dept;
END;

DECLARE
    CURSOR c_dept IS SELECT dept_id FROM departments;
    CURSOR c_emp(p_dept_id NUMBER) IS
        SELECT employee_id FROM employees WHERE dept_id = p_dept_id;
    v_dept departments.dept_id%TYPE;
    v_emp  employees.employee_id%TYPE;
BEGIN
    OPEN c_dept;
    LOOP
        FETCH c_dept INTO v_dept;
        EXIT WHEN c_dept%NOTFOUND;
        OPEN c_emp(v_dept);
        LOOP
            FETCH c_emp INTO v_emp;
            EXIT WHEN c_emp%NOTFOUND;
            process_employee(v_emp);
        END LOOP;
        CLOSE c_emp;  -- ループの外でかつ内側ループの後に確実にCLOSE
    END LOOP;
    CLOSE c_dept;
END;

-- 明示的カーソルで FOR ループを使う場合
BEGIN
    FOR r IN (SELECT employee_id, first_name FROM employees WHERE dept_id = 10)
    LOOP
        -- カーソルのOPEN/FETCH/CLOSEはOracleが自動処理
        DBMS_OUTPUT.PUT_LINE(r.employee_id || ': ' || r.first_name);
    END LOOP;
    -- ループ終了（または例外発生）時に自動的にCLOSEされる
END;

DECLARE
    TYPE t_emp_tbl IS TABLE OF employees%ROWTYPE;
    l_emps t_emp_tbl;
BEGIN
    -- LIMIT 1000 で1000件ずつ取得し、カーソルを長時間保持しない
    SELECT * BULK COLLECT INTO l_emps
    FROM employees
    WHERE dept_id = 10;

    FOR i IN 1..l_emps.COUNT LOOP
        process_employee(l_emps(i).employee_id);
    END LOOP;
END;

DECLARE
    l_cur SYS_REFCURSOR;
    v_val VARCHAR2(100);
BEGIN
    FOR i IN 1..500 LOOP
        -- 毎回 OPEN しているが CLOSE していない
        OPEN l_cur FOR 'SELECT name FROM items WHERE id = ' || i;
        FETCH l_cur INTO v_val;
        -- CLOSE l_cur; ← これが抜けている
    END LOOP;
    -- 500個のカーソルが開いたまま → ORA-01000
END;

DECLARE
    l_cur SYS_REFCURSOR;
    v_val VARCHAR2(100);
BEGIN
    FOR i IN 1..500 LOOP
        OPEN l_cur FOR
            SELECT name FROM items WHERE id = :id
            USING i;  -- バインド変数を使う
        FETCH l_cur INTO v_val;
        CLOSE l_cur;  -- 毎回確実に CLOSE
    END LOOP;
END;

// BAD: ループのたびに PreparedStatement を作成するが close しない
public void processBatch(Connection conn, List<Integer> ids) throws SQLException {
    for (int id : ids) {
        PreparedStatement ps = conn.prepareStatement(
            "SELECT name FROM items WHERE id = ?"
        );
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        // rs も ps も close していない → カーソルが蓄積
        while (rs.next()) {
            process(rs.getString(1));
        }
    }
    // ids が 300 件を超えると ORA-01000
}

// GOOD: try-with-resources により例外発生時も確実に close される
public void processBatch(Connection conn, List<Integer> ids) throws SQLException {
    // PreparedStatement はループの外で1度だけ作成するのがベスト
    try (PreparedStatement ps = conn.prepareStatement(
            "SELECT name FROM items WHERE id = ?")) {
        for (int id : ids) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    process(rs.getString(1));
                }
            } // ResultSet は try-with-resources で自動 close
        }
    } // PreparedStatement も自動 close
}

// 接続プール（HikariCP/DBCP等）を使う場合も同様
// Connection.close() は物理切断ではなくプールへの返却だが、
// 未 close の PreparedStatement はカーソルとして残るため必ず close すること

public void safeBatchInsert(DataSource ds, List<Item> items) throws SQLException {
    try (Connection conn = ds.getConnection();
         PreparedStatement ps = conn.prepareStatement(
             "INSERT INTO items (id, name) VALUES (?, ?)")) {
        for (Item item : items) {
            ps.setInt(1, item.getId());
            ps.setString(2, item.getName());
            ps.addBatch();
        }
        ps.executeBatch();
        conn.commit();
    } // Connection も PreparedStatement も自動 close
}

import oracledb  # または cx_Oracle

conn = oracledb.connect(user="hr", password="pw", dsn="localhost/orcl")

# with 文を使うと例外発生時も確実にカーソルが close される
with conn.cursor() as cursor:
    cursor.execute("SELECT employee_id, first_name FROM employees WHERE dept_id = :1", [10])
    for row in cursor:
        print(row)

# with ブロックを抜けると cursor.close() が自動実行される
conn.close()

-- 現在の値を確認
SHOW PARAMETER open_cursors;

-- 動的に変更（再起動不要。ただし再起動すると spfile の値に戻る）
ALTER SYSTEM SET open_cursors = 1000;

-- spfile に永続化（再起動後も有効）
ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH;

-- セッション単位で変更することはできない（システム全体パラメータ）
-- ALTER SESSION SET open_cursors = 1000; → エラーになる

DECLARE
    CURSOR c_orders IS
        SELECT order_id, amount FROM orders WHERE status = 'PENDING';
    v_order orders%ROWTYPE;
BEGIN
    OPEN c_orders;
    LOOP
        FETCH c_orders INTO v_order.order_id, v_order.amount;
        EXIT WHEN c_orders%NOTFOUND;
        process_order(v_order.order_id, v_order.amount);
    END LOOP;
    CLOSE c_orders;

EXCEPTION
    WHEN OTHERS THEN
        -- カーソルがオープン中であれば必ず CLOSE する
        IF c_orders%ISOPEN THEN
            CLOSE c_orders;
        END IF;
        -- エラー情報を記録してから再 RAISE
        INSERT INTO error_log (error_msg, created_at)
        VALUES (SQLERRM, SYSDATE);
        RAISE;
END;