Oracle 即時擷取前端正在執行的 SQL：V$OPEN_CURSOR 與 V$SESSION 聯查實戰

問題背景

生產環境出現效能抖動或鎖定等待時，DBA 需要「當下」看到前端應用程式正在發送什麼 SQL。Oracle 提供 V$OPEN_CURSOR 與 V$SESSION 動態效能視圖，可即時關聯活躍會話與其開啟的游標 SQL 文本。

環境資訊

• OS：Windows 2000
• DB：Oracle 8.1.6
• 安裝路徑：C:\ORANT

核心查詢邏輯

1. 從 V$SESSION 篩選 狀態 = 'ACTIVE' 的會話 (SID, SERIAL#)
2. 以 SID 關聯 V$OPEN_CURSOR 取得該會話正開啟的游標 SQL 文本

標準查詢語句

SELECT user_name, sql_text
FROM v$open_cursor
WHERE sid IN (
    SELECT sid
    FROM v$session
    WHERE status = 'ACTIVE'
);

視圖結構說明

進階增強版：取得完整 SQL 文本

V$OPEN_CURSOR.SQL_TEXT 僅顯示前 60 字元。若需完整文本，需關聯 V$SQLTEXT_WITH_NEWLINES：

SELECT s.sid, s.serial#, s.username, s.status, t.sql_text
FROM v$session s
JOIN v$sqltext_with_newlines t
  ON t.hash_value = s.sql_hash_value
 AND t.address = s.sql_address
WHERE s.status = 'ACTIVE'
ORDER BY t.address, t.piece;

實戰應用場景

1. 鎖定爭用分析：結合 V$LOCK、V$SESSION_WAIT 找出持有鎖的 SQL
2. 失控查詢終止：識別長時間 ACTIVE 且高資源消耗的 SID，執行 ALTER SYSTEM KILL SESSION 'sid,serial#'
3. 應用行為審計：記錄特定用戶/模組的實際 SQL 模式，驗證是否符合預期

權限需求

執行查詢需具備 SELECT ANY DICTIONARY 或 SELECT_CATALOG_ROLE 權限。生產環境建議建立專用監控帳號，僅授予必要視圖查詢權限。

注意事項

• V$OPEN_CURSOR 反映「已開啟但未關閉」的游標，包含已執行完成待提交/回滾、或正在擷取資料的游標
• STATUS = 'ACTIVE' 表示會話正在執行呼叫（SQL 執行中、網路傳輸中、PL/SQL 執行中），非交易狀態
• 高併發環境下查詢本身會增加閂鎖開銷，建議限制查詢頻率（如 10 秒一次）

總結

掌握 V$SESSION + V$OPEN_CURSOR (+ V$SQLTEXT) 聯查技巧，是 DBA 即時診斷生產環境異常的「聽診器」。將此查詢封裝為監控腳本或納入自訂儀表板，可大幅縮短故障定位時間。