查看 Oracle 資料庫 SQL 執行歷史的兩大核心方法：LogMiner 與 AWR 歷史視圖

查看 Oracle 資料庫 SQL 執行歷史的兩大核心方法：LogMiner 與 AWR 歷史視圖

方法一：LogMiner（歸檔/線上日誌深度挖掘）

優點：可還原完整 SQL 文本、綁定變數、執行用戶、時間點，甚至 DML 逐行變更（補充日誌）。
缺點：需啟用歸檔模式、配置補充日誌、掌握較複雜的 DBMS_LOGMNR 套件操作；若無歸檔僅能挖掘線上日誌（時間窗極短）。

快速啟動範例

-- 1. 啟用補充日誌（一次性）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 2. 建立 LogMiner 字典（選項：線上目錄 / 扁平檔 / 歸檔中）
EXEC DBMS_LOGMNR_D.BUILD(OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE, DICTIONARY_FILENAME => 'dict.ora', DICTIONARY_LOCATION => '/u01/logmnr');

-- 3. 添加要挖掘的日誌檔
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/arch_1_1234.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/arch_1_1235.dbf', OPTIONS => DBMS_LOGMNR.ADDFILE);

-- 4. 啟動挖掘
EXEC DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/u01/logmnr/dict.ora', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

-- 5. 查詢 V$LOGMNR_CONTENTS
SELECT TIMESTAMP, USERNAME, SQL_REDO, SEG_OWNER, SEG_NAME, OPERATION
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SCOTT' AND OPERATION IN ('INSERT','UPDATE','DELETE')
ORDER BY TIMESTAMP;

-- 6. 結束
EXEC DBMS_LOGMNR.END_LOGMNR();

方法二：AWR 歷史視圖（DBA_HIST_*）—— 簡便、無額外配置

優點：Enterprise Edition + Diagnostic Pack 授權即可用，標準 SQL 查詢，保留期可配置（預設 8 天）。
缺點：僅捕捉高負載 SQL（Top SQL），非全量 SQL；綁定變數可能被隱藏；保留期過後不可查。

經典查詢：近 3 天執行過 DELETE/TRUNCATE 的用戶、程式、SQL 文本

SELECT 
  c.USERNAME,
  a.PROGRAM,
  b.SQL_TEXT,
  b.COMMAND_TYPE,
  a.SAMPLE_TIME
FROM DBA_HIST_ACTIVE_SESS_HISTORY a
JOIN DBA_HIST_SQLTEXT b ON a.SQL_ID = b.SQL_ID
JOIN DBA_USERS c ON a.USER_ID = c.USER_ID
WHERE a.SAMPLE_TIME BETWEEN SYSDATE - 3 AND SYSDATE
  AND b.COMMAND_TYPE IN (7, 85)  -- 7=DELETE, 85=TRUNCATE TABLE
ORDER BY a.SAMPLE_TIME DESC;

Oracle 10.2+ COMMAND_TYPE 完整對照表（節選高頻）

進階技巧：結合 V$SQL / V$SQLAREA 即時補充

-- 當前庫存 SQL（含完整文本、執行計劃、綁定變數）
SELECT SQL_ID, SQL_FULLTEXT, EXECUTIONS, ELAPSED_TIME/1E6 ELAPSED_SEC
FROM V$SQLAREA
WHERE PARSING_SCHEMA_NAME = 'SCOTT'
  AND SQL_TEXT LIKE '%SENSITIVE_TABLE%'
ORDER BY LAST_ACTIVE_TIME DESC;

選型建議

• 審計、合規、數據變更回溯 → LogMiner（配合 GoldenGate/補充日誌）。
• 效能調優、異常 SQL 定位、近期操作審查 → AWR 歷史視圖 + V$SQL。
• 無診斷包授權 → 自建 DBA_HIST_SQLTEXT 定時快照腳本或使用 Statspack。

掌握雙軌查詢能力，從「發生了什麼 SQL」到「SQL 執行了什麼效果」全鏈路可視。