善用 Oracle 外部表快速查詢 Alert Log 報警日誌

什麼是外部表？

外部表 讓資料庫能直接用 SQL 查詢作業系統平面檔。其結構定義存於資料字典，實際資料仍置於 OS 檔案中。主要特性：

• 唯讀：不支援 DML（INSERT/UPDATE/DELETE）與索引建立。
• 適用場景：ETL 載入前預覽、日誌分析、跨平台資料交換。

建立第一個外部表：分步實作

1. 準備 OS 資料檔

mkdir -p /Oracle/ext
cat > /Oracle/ext/ext.dat <<EOF
10,20,30
40,50,60
70,80,90
EOF

2. 建立使用者與目錄物件

CREATE USER test IDENTIFIED BY "123" DEFAULT TABLESPACE test QUOTA UNLIMITED ON test;
GRANT CREATE ANY DIRECTORY TO test;
CONN test/123
CREATE DIRECTORY ext AS '/Oracle/ext';

3. 定義外部表

CREATE TABLE exttable (
  id   NUMBER,
  name VARCHAR2(10),
  i    NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
  )
  LOCATION ('ext.dat')
);

4. 測試查詢

SELECT * FROM exttable;
-- 即可看到三筆資料，驗證成功。

進階應用：直接查詢 Alert Log

Alert Log 位於 $ORACLE_BASE/admin//bdump/alert_.log，同樣可建外部表即時分析。

1. 建立目錄指向 bdump

CREATE DIRECTORY bdump AS '/oracle/u01/app/oracle/admin/db2/bdump';

2. 定義單欄寬字串外部表

CREATE TABLE alert_log (
  text VARCHAR2(400)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY bdump
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
  )
  LOCATION ('alert_db2.log')
);

3. 實戰查詢範例

• SELECT * FROM alert_log WHERE ROWNUM < 10; 快速預覽啟動訊息。
• SELECT * FROM alert_log WHERE text LIKE 'ORA-%'; 秒級過濾所有錯誤代碼，無需 grep、vi 來回切換。

優勢總結

外部表將 OS 檔案納入 SQL 引擎，配合 WHERE、REGEXP_LIKE、分析函數等強大功能，讓 DBA 能以集合思維處理日誌，大幅縮短故障排查時間。注意：外部表不支援並行查詢（12c 起支援），大型日誌建議先切分檔案或搭配 DBMS_SCHEDULER 定期載入暫存表。