# OS 層面 (Linux 示例)
mkdir -p /u01/ext_data
chown oracle:oinstall /u01/ext_data
chmod 750 /u01/ext_data
# 放入測試文件 ext.dat
cat > /u01/ext_data/ext.dat <<'EOF'
10,20,30
40,50,60
70,80,90
EOF-- 建立用戶 (可選)
CREATE USER ext_user IDENTIFIED BY "pwd" DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE ANY DIRECTORY TO ext_user;
-- 建立 Directory 物件 (映射 OS 路徑)
CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/ext_data';
GRANT READ, WRITE ON DIRECTORY ext_dir TO ext_user;CONNECT ext_user/pwd
CREATE TABLE ext_table (
id NUMBER,
name VARCHAR2(10),
val NUMBER
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION ('ext.dat')
)
REJECT LIMIT UNLIMITED; -- 或具體數字
-- 查詢驗證
SELECT * FROM ext_table;
-- 輸出三行數據-- 1. 確定告警日誌路徑
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
-- 典型：/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
-- 2. 建立 Directory 指向 trace 目錄
CREATE OR REPLACE DIRECTORY alert_dir AS '/u01/app/oracle/diag/rdbms/orcl/ORCL/trace';
-- 3. 建立外部表 (定寬/非結構化文本，按行讀取)
CREATE TABLE alert_log_ext (line VARCHAR2(4000))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY alert_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE NOLOGFILE NODISCARDFILE
FIELDS (line CHAR(4000))
)
LOCATION ('alert_ORCL.log')
)
REJECT LIMIT UNLIMITED;
-- 4. 查詢最近 100 行錯誤
SELECT * FROM alert_log_ext
WHERE line LIKE '%ORA-%' OR line LIKE '%ERROR%'
ORDER BY ROWNUM DESC
FETCH FIRST 100 ROWS ONLY;| 限制 | 說明 | 規避 |
|---|---|---|
| 不支援 DML (INSERT/UPDATE/DELETE) | 唯讀物件 | `CREATE TABLE real AS SELECT * FROM ext` 落地後再操作 |
| 不支援索引 | 無段塊 | 落地表再建索引 |
| 並行查詢需文件可分割 | 單文件無法並行 | 大文件拆分為多個 (ext_1.dat, ext_2.dat...) LOCATION 列出所有 |
| 字符集不匹配導致亂碼 | 文件編碼 ≠ DB 字符集 | ACCESS PARAMETERS 指定 `CHARACTERSET AL32UTF8` 等 |
| 文件變更需手動刷新 | 無自動感知 | 查詢前執行 `ALTER TABLE ext_table LOCATION ('new.dat')` 或重建 |
-- 導出為外部表 dump 文件 (可跨平台/版本遷移)
CREATE TABLE ext_dp_table
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dp_dir
LOCATION ('exp_%U.dmp')
)
AS SELECT * FROM big_table;
-- 目標庫建立同名 Directory、複製 dump 文件、建立同結構外部表即可查詢
-- 優點：壓縮、並行、類型保真、無解析開銷外部表是 **「SQL 查詢 OS 文件」的橋樑**，將 ETL 從「工具導入」簡化為「一條 SQL」。掌握 `ORACLE_LOADER` (文本/CSV/JSON) 與 `ORACLE_DATAPUMP` (二進制高效遷移) 兩大類型、Directory 權限模型、ACCESS PARAMETERS 精細控制，能構建極簡、高效、可維運的資料接入管道。
