Oracle 游標監控查詢：V$OPEN_CURSOR 實戰腳本與參數調優

游標洩漏是導致 ORA-01000: maximum open cursors exceeded 的主因。透過 V$OPEN_CURSOR、V$SESSION、V$SQL 聯查，可精準定位高佔用會話、洩漏 SQL 與調整 OPEN_CURSORS 參數。本文提供生產可用的監控腳本、分析邏輯與調優建議。

核心監控查詢

1. 依用戶統計游標數 (降序)

SELECT o.sid,
       s.osuser,
       s.machine,
       s.program,
       COUNT(*) AS open_cursors
FROM   v$open_cursor o
JOIN   v$session s ON o.sid = s.sid
WHERE  s.username = 'CPORTAL'  -- 移除此行查全庫
GROUP  BY o.sid, s.osuser, s.machine, s.program
ORDER  BY open_cursors DESC;

用途： 快速鎖定「哪個會話、哪台機器、哪個程式」開啟最多游標。

2. 查詢資料庫層級參數設定

SHOW PARAMETER open_cursors;
-- 或
SELECT name, value FROM v$parameter WHERE name = 'open_cursors';

預設通常 300，高併發系統建議調大至 1000-3000 (需修改 SPFILE 重啟)。

3. 定位特定會話正在執行的 SQL 文本

SELECT q.sql_text
FROM   v$open_cursor o
JOIN   v$sql q ON q.hash_value = o.hash_value
              AND q.child_number = o.child_number  -- 11g+ 需加 child_number
WHERE  o.sid = 217;  -- 替換為目標 SID

可見該會話目前持有哪些游標對應的 SQL，判斷是否為未關閉的 ResultSet、迴圈未釋放等。

4. 全庫游標總數快照

SELECT COUNT(*) AS total_open_cursors FROM v$open_cursor;

定期採集 (如每 5 分鐘) 寫入監控表，繪製趨勢圖，預警異常增長。

進階診斷：找出洩漏的具體程式碼路徑

SELECT s.sid,
       s.serial#,
       s.username,
       s.osuser,
       s.machine,
       s.program,
       s.module,
       s.action,
       s.sql_id,
       q.sql_text,
       o.cursor_type
FROM   v$open_cursor o
JOIN   v$session s ON o.sid = s.sid
JOIN   v$sql q     ON q.sql_id = o.sql_id
WHERE  s.username IS NOT NULL
ORDER  BY s.sid, o.cursor_type;

MODULE/ACTION 若由應用正確設定 (DBMS_APPLICATION_INFO.SET_MODULE)，可直達業務功能點。

PL/SQL 開發規範：防洩漏清單

參數調優建議

1. 調大 OPEN_CURSORS：ALTER SYSTEM SET OPEN_CURSORS = 2000 SCOPE=SPFILE; 重啟生效。觀察高峰期實際峰值，設為峰值 1.5-2 倍。
2. 啟用 SESSION_CACHED_CURSORS：ALTER SYSTEM SET SESSION_CACHED_CURSORS = 100 SCOPE=SPFILE; 減少重複解析，間接降低游標開啟頻率。
3. 設定資源限制 Profile：

   CREATE PROFILE dev_limit LIMIT
     SESSIONS_PER_USER 10
     OPEN_CURSORS 500;  -- 針對開發帳號限制
   ALTER USER dev_user PROFILE dev_limit;

自動化監控腳本 (Shell + SQL*Plus)

#!/bin/bash
# check_open_cursors.sh
ORACLE_SID=ORCL
export ORACLE_SID
THRESHOLD=800  # 告警閾值

sqlplus -s / as sysdba <