Oracle 即時追蹤前端發出的 SQL：v$open_cursor 與 v$session 實戰查詢

當需要**即時掌握**應用程式正在資料庫執行什麼 SQL、哪個使用者、哪個 Session，`v$open_cursor` 配合 `v$session` 是最直接的動態視圖組合。

核心視圖結構

查詢 1：所有作用中 Session 正在執行的 SQL (簡版)

SELECT s.sid, s.serial#, s.username, s.program, s.machine, 
       oc.sql_text
FROM v$session s
JOIN v$open_cursor oc ON oc.sid = s.sid
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.sid;

**注意**：`v$open_cursor.SQL_TEXT` 僅前 60 字元，完整 SQL 需關聯 `v$sqltext_with_newlines`。

查詢 2：特定使用者完整 SQL 文本

SELECT sql_text
FROM v$sqltext_with_newlines
WHERE (hash_value, address) IN (
  SELECT sql_hash_value, sql_address
  FROM v$session
  WHERE username = UPPER('&username')
)
ORDER BY address, piece;

執行時輸入使用者名 (不區分大小寫)，輸出完整 SQL (含換行)。

查詢 3：Session 級詳細追蹤 (含等待事件、物理讀、邏輯讀)

SELECT s.sid, s.serial#, s.username, s.status, s.program, s.module, s.action,
       s.sql_hash_value, s.sql_address,
       s.event, s.wait_class, s.seconds_in_wait,
       s.blocking_session,
       oc.sql_text AS current_cursor_sql,
       sq.sql_fulltext AS full_sql_text
FROM v$session s
LEFT JOIN v$open_cursor oc ON oc.sid = s.sid AND oc.hash_value = s.sql_hash_value
LEFT JOIN v$sql sq ON sq.hash_value = s.sql_hash_value AND sq.address = s.sql_address
WHERE s.status = 'ACTIVE'
  AND s.type = 'USER'
ORDER BY s.sid;

**關鍵增強**：`v$sql.SQL_FULLTEXT` (CLOB) 取得完整 SQL；`EVENT`/`WAIT_CLASS` 顯示等待類型 (如 `db file scattered read`、`log file sync`、`enq: TX - row lock contention`)；`BLOCKING_SESSION` 直接定位阻塞鏈。

查詢 4：游標洩漏與重複游標偵測

-- 單一 Session 開啟游標數
SELECT sid, COUNT(*) AS open_cursors
FROM v$open_cursor
GROUP BY sid
HAVING COUNT(*) > 50  -- 閾值依 OPEN_CURSORS 參數調整
ORDER BY 2 DESC;

-- 重複 SQL 文本開啟多游標 (未正確關閉/重用)
SELECT sid, sql_text, COUNT(*) AS dup_count
FROM v$open_cursor
GROUP BY sid, sql_text
HAVING COUNT(*) > 5
ORDER BY 3 DESC;

實戰場景應用

權限需求

• 需 `SELECT_CATALOG_ROLE` 或個別 `SELECT ON V_$SESSION`、`V_$OPEN_CURSOR`、`V_$SQLTEXT`、`V_$SQL`。
• 生產環境建立專用監控帳號 `MONITOR_USER` 授予上述權限，**嚴禁使用 SYS/SYSTEM 直接登入查詢**。

局限性與補充

• `v$open_cursor` 僅顯示**目前開啟**的游標，**已關閉的游標不保留歷史** (需 AWR/ASH/審計)。
• `SQL_TEXT` 截斷 60 字元，完整文本必須關聯 `v$sql`/`v$sqltext`。
• RAC 環境：視圖為 **Instance 級**，需在各節點執行或使用 `GV$` 全域視圖 (`GV$SESSION` 含 `INST_ID`)。