利用 Oracle 外部表查詢 Alert Log：將文字日誌變為可 SQL 分析的資料集

優勢

• 以 SELECT 查詢告警日誌，支援 WHERE、ORDER BY、REGEXP 過濾錯誤。
• 無需複製檔案、無需 OS 權限，資料庫內即可完成分析。
• 即時反映最新寫入（外部表讀取當前檔案內容）。

實作步驟

1. 建立專用使用者與權限

CREATE USER checker IDENTIFIED BY pwd DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY TO checker;

2. 確認 Alert Log 路徑並建立 DIRECTORY

CONN checker/pwd
SHOW PARAMETER background_dump_dest;
-- 假設回傳 /u01/app/oracle/diag/rdbms/orcl/orcl/trace

CREATE DIRECTORY bdump AS '/u01/app/oracle/diag/rdbms/orcl/orcl/trace';

3. 建立外部表

CREATE TABLE alert_log (text VARCHAR2(4000))
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY bdump
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    NOBADFILE NOLOGFILE NODISCARDFILE
  )
  LOCATION ('alert_orcl.log')
)
REJECT LIMIT UNLIMITED;

關鍵參數：RECORDS DELIMITED BY NEWLINE 逐行讀取；NOBADFILE 等簡化輸出。

4. 查詢範例

-- 全部內容
SELECT text FROM alert_log;

-- 僅錯誤行
SELECT text FROM alert_log WHERE text LIKE 'ORA-%';

-- 最近 50 行（依 ROWID 排序近似時間序）
SELECT * FROM (
  SELECT text FROM alert_log ORDER BY ROWID DESC
) WHERE ROWNUM < 50;

-- 統計每日 ORA- 錯誤數
SELECT TRUNC(TO_DATE(SUBSTR(text,1,19),'DY MON DD HH24:MI:SS YYYY')) dt, COUNT(*)
FROM alert_log
WHERE text LIKE 'ORA-%'
GROUP BY TRUNC(TO_DATE(SUBSTR(text,1,19),'DY MON DD HH24:MI:SS YYYY'))
ORDER BY dt DESC;

外部表限制與注意

• 唯讀：不支援 DML、索引、約束。
• 單一分隔符：同一檔案不可混用逗號/分號等。
• 刪除順序：先 DROP TABLE 再 DROP DIRECTORY，否則殘留中繼資料。
• DBA_EXTERNAL_LOCATIONS 可查詢所有外部表對應檔案。
• 大檔案（>2GB）建議定期輪轉 Alert Log，避免全表掃描過慢。

進階：結合 DBMS_SCHEDULER 定期巡檢

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'CHECK_ALERT_LOG',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE v_cnt NUMBER;
      BEGIN
        SELECT COUNT(*) INTO v_cnt FROM alert_log
        WHERE text LIKE 'ORA-%' AND text NOT LIKE '%ORA-00000%'; -- 排除正常訊息
        IF v_cnt > 0 THEN
          -- 發送郵件/簡訊告警 (略)
          NULL;
        END IF;
      END;
    ]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    enabled         => TRUE
  );
END;
/

結語

外部表將 Alert Log 轉化為可查詢資料表，配合 SQL 強大分析能力與排程自動化，實現「日誌即資料、資料即洞察」。建議納入標準化巡檢體系。