Java 開發連線 Oracle 避坑指南：ORA-01000 最大游標超限根因與最佳實踐

錯誤現象

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

表示當前 Session 開啟的游標數超過 OPEN_CURSORS 參數預設值 (通常 300)。

根因：程式碼資源洩漏

每次呼叫 conn.createStatement()、conn.prepareStatement()、stmt.executeQuery() 都會在資料庫端開啟一個游標。若在迴圈中重複建立且未關閉，游標迅速耗盡。

典型錯誤程式碼

// ❌ 錯誤：迴圈內重複建立 Statement，未關閉
for (String id : ids) {
    Statement stmt = conn.createStatement(); // 每次新游標
    ResultSet rs = stmt.executeQuery("SELECT * FROM t WHERE id = " + id);
    // 處理 rs...
    // 缺少 rs.close(), stmt.close()
}

正確模式：資源管理最佳實踐

1. Statement/PreparedStatement 提升至迴圈外

// ✅ 正確：PreparedStatement 重複使用
String sql = "SELECT * FROM t WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    for (String id : ids) {
        pstmt.setString(1, id);
        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) { /* 處理 */ }
        } // rs 自動關閉
    }
} // pstmt 自動關閉

2. Java 7+ try-with-resources 自動關閉 (強烈推薦)

try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setInt(1, 100);
    try (ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) { /* ... */ }
    }
} // 所有資源自動關閉，即使發生異常

3. 連線池設定驗證

• 確保連線池 maxTotal ≤ Oracle PROCESSES / SESSIONS 限制。
• 啟用 testOnBorrow、validationQuery="SELECT 1 FROM DUAL" 避免殭屍連線。
• 設定 removeAbandonedTimeout 回收洩漏連線 (Druid/HikariCP/PoolingDataSource)。

為什麼單純調大 OPEN_CURSORS 不是好辦法？

檢測與監控 SQL

-- 當前 Session 開啟游標數
SELECT a.value AS open_cursors, s.sid, s.username, s.program
FROM v$sesstat a
JOIN v$statname b ON a.statistic# = b.statistic#
JOIN v$session s ON a.sid = s.sid
WHERE b.name = 'opened cursors current'
ORDER BY a.value DESC;

-- 系統參數
SHOW PARAMETER open_cursors;

總結檢查清單

1. 所有 Statement、PreparedStatement、ResultSet 是否在 finally 或 try-with-resources 中關閉？
2. 迴圈內是否重複建立 Statement？改為 PreparedStatement 綁定變數重用。
3. 批次處理是否使用 addBatch() / executeBatch() 減少往返？
4. 連線池大小是否合理？是否啟用洩漏偵測？
5. 是否有長時間持有 ResultSet (如網頁分頁未關閉游標)？