Oracle 外部表（External Table）

适用版本：Oracle Database 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

外部表 读取数据库外的数据文件[1]：

特点：

只读
元数据在数据库
数据在文件
类似普通表查询

2. 创建

2.1 目录对象

CREATE DIRECTORY ext_data_dir AS '/u01/data';
GRANT READ, WRITE ON DIRECTORY ext_data_dir TO scott;

2.2 创建外部表

CREATE TABLE ext_employees (
  employee_id NUMBER,
  last_name VARCHAR2(100),
  salary NUMBER,
  hire_date DATE
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      employee_id CHAR,
      last_name CHAR,
      salary CHAR,
      hire_date CHAR DATE_FORMAT DATE MASK 'YYYY-MM-DD'
    )
  )
  LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

2.3 使用

SELECT * FROM ext_employees;
SELECT * FROM ext_employees WHERE salary > 5000;

3. 数据文件

3.1 CSV 示例

100,Smith,5000,2026-01-15
101,Jones,6000,2026-02-20
102,Brown,5500,2026-03-10

3.2 多文件

LOCATION ('file1.csv', 'file2.csv', 'file3.csv')

4. ACCESS PARAMETERS

4.1 记录分隔

RECORDS DELIMITED BY NEWLINE
RECORDS DELIMITED BY '|'

4.2 字段分隔

FIELDS TERMINATED BY ','
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

4.3 行格式

FIXED 100  -- 固定长度
VARIABLE 5  -- 变长，前 5 字节为长度

5. ORACLE_DATAPUMP

5.1 导出

CREATE TABLE exp_employees
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY ext_data_dir
  LOCATION ('employees.dmp')
)
AS SELECT * FROM employees;

5.2 读取

-- 在另一个数据库
CREATE TABLE imp_employees (
  employee_id NUMBER,
  last_name VARCHAR2(100),
  salary NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY ext_data_dir
  LOCATION ('employees.dmp')
);

6. 查看日志

-- 日志文件
SELECT * FROM ext_log;
-- 坏数据文件
SELECT * FROM ext_bad;

7. 应用场景

7.1 数据加载

-- ETL：外部表 → 内部表
INSERT INTO employees 
SELECT * FROM ext_employees;

7.2 数据导出

-- 导出为 Data Pump 格式
CREATE TABLE exp_sales
ORGANIZATION EXTERNAL (...) 
AS SELECT * FROM sales WHERE sale_date >= '2026-01-01';

7.3 报表

-- 直接查询文件
SELECT * FROM ext_log WHERE log_date >= SYSDATE - 1;

8. 限制

只读（不能 DML）
不能索引
不能约束
性能取决于文件 I/O

9. 常见坑与排错

9.1 ORA-29913: 执行出错

-- 检查目录权限
-- 检查文件路径
-- 查看日志文件

9.2 ORA-30653: 拒绝限制

-- 坏数据过多
-- 检查格式
-- 提高 REJECT LIMIT

9.3 性能差

-- 1. 并行
CREATE TABLE ext_emp (...) PARALLEL 4;
-- 2. 分文件
-- 3. 限制数据量

10. 最佳实践

数据加载用外部表：替代 SQL*Loader
并行提升性能：大文件
Data Pump 跨库：高效
合理分隔符：避免歧义
错误日志：排查
目录权限管理：安全
定期清理：日志/坏文件
监控性能：I/O

11. 参考资料

[1] Oracle Database Utilities 19c, “External Tables” https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/external-tables.html