Oracle 外部表實務心得：目錄物件、唯讀限制與跨平台路徑大小寫坑

什麼是外部表？

外部表讓資料庫像查詢視圖一樣，直接對 OS 上的文字檔（CSV、定寬、ORACLE_DATAPUMP 等格式）執行 SELECT，無需先載入資料庫。特性：

• 唯讀（不可 DML）。
• 支援並行查詢、分區裁剪、管道化函數。
• 常用於 ETL 階段、日誌分析、跨平台資料交換。

核心前置作業：建立目錄物件

CREATE OR REPLACE DIRECTORY ext_data_dir AS '/data/ext_files';  -- Linux
-- 或
CREATE OR REPLACE DIRECTORY ext_data_dir AS 'D:\ext_files';     -- Windows

三大坑點：

1. 權限：唯有 CREATE ANY DIRECTORY 權限者（通常為 DBA）可建立，一般使用者需被授權 READ/WRITE。
2. 路徑不驗證：資料庫建立目錄物件時不檢查 OS 路徑是否存在，拼錯路徑仍建立成功，查詢時才報錯 KUP-04040。
3. 大小寫敏感：Linux 區分大小寫，Windows 不區分。建議統一採用小寫路徑與檔名，跨平台遷移零修改。

建立外部表範例

CREATE TABLE ext_sales (
  sale_date DATE,
  product_id NUMBER,
  amount NUMBER(10,2)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' 
    MISSING FIELD VALUES ARE NULL
    (sale_date CHAR(10) DATE_FORMAT DATE MASK "YYYY-MM-DD",
     product_id,
     amount)
  )
  LOCATION ('sales_202401.csv')
)
REJECT LIMIT UNLIMITED;

常見限制提醒

• 不支援索引、約束、觸發器、LOB 欄位（12c+ 部分放寬）。
• 查詢時每次讀取檔案，大檔案頻繁查詢建議先 CTAS 導入暫存表。
• 檔案編碼需與資料庫字元集一致，否則出現亂碼或 KUP-04021。