Oracle 外部表實戰：零代碼讀取告警日志、CSV、JSON 等 OS 文件並加載入庫

外部表核心特性

• 元數據存數據字典，數據留在 OS 文件（只讀）。
• 支援 SELECT、JOIN、CTAS 載入內表，**不可 DML/建索引**。
• 讀取引擎：ORACLE_LOADER（文本/CSV）、ORACLE_DATAPUMP（二進制 dump）、ORACLE_HIVE/ORACLE_HDFS（大數據）。

五步建立告警日志外部表

1. 準備 OS 文件與目錄

# Linux
mkdir -p /oracle/ext
# 假設 alert.log 已存在或定期轉儲至此

2. 建立目錄對象並授權

CREATE USER ext_user IDENTIFIED BY "Pass#123" DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE ANY DIRECTORY TO ext_user;

CONN ext_user/Pass#123
CREATE DIRECTORY ext_dir AS '/oracle/ext';
GRANT READ, WRITE ON DIRECTORY ext_dir TO ext_user;

3. 定義外部表結構（針對告警日志半結構化文本）

CREATE TABLE alert_log_ext (
    log_date   VARCHAR2(30),   -- 如 "2024-05-17 10:30:45"
    severity   VARCHAR2(10),   -- ERROR/WARNING/INFO
    module     VARCHAR2(50),   -- 產生模組
    message    VARCHAR2(4000)  -- 正文
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        LOGFILE  ext_dir:'alert_load.log'
        BADFILE  ext_dir:'alert_load.bad'
        DISCARDFILE ext_dir:'alert_load.dsc'
        FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL FIELDS
    )
    LOCATION ('alert.log')
)
REJECT LIMIT UNLIMITED;

關鍵參數解析：

• FIELDS TERMINATED BY '|'：假設預處理腳本已將日志規範為管道分隔。
• REJECT LIMIT UNLIMITED：允許所有錯誤行進 BADFILE，不中斷查詢。
• LOGFILE/BADFILE/DISCARDFILE：自動生成載入診斷文件。

4. 直接查詢驗證

SELECT * FROM alert_log_ext WHERE severity = 'ERROR' AND log_date > '2024-05-01';

5. 高效載入內部表（CTAS + 並行）

CREATE TABLE alert_log_int
PARTITION BY RANGE (log_dt) (
    PARTITION p_202405 VALUES LESS THAN (DATE '2024-06-01'),
    PARTITION p_202406 VALUES LESS THAN (DATE '2024-07-01')
)
PARALLEL 4
AS
SELECT
    TO_DATE(SUBSTR(log_date,1,19), 'YYYY-MM-DD HH24:MI:SS') AS log_dt,
    severity, module, message
FROM alert_log_ext;

進階場景：JSON 文件直接查詢（12c+）

CREATE TABLE json_ext (doc CLOB)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY '}{'   -- 假設每行一個 JSON 對象
        FIELDS (doc CHAR(10000))
    )
    LOCATION ('events.json')
);

-- 18c+ 直接 SQL/JSON 查詢
SELECT jt.*
FROM json_ext je,
     JSON_TABLE(je.doc, '$' COLUMNS (
         event_id   NUMBER PATH '$.id',
         event_type VARCHAR2(20) PATH '$.type',
         payload    VARCHAR2(4000) PATH '$.payload'
     )) jt;

效能優化與避坑

對比：外部表 vs SQL*Loader vs Data Pump

外部表將「OS 文件」變為「可查詢關係表」，是構建 Data Lake、日志審計、異構數據接入的輕量級利器。