DB / Oracle / 底层与基础

Oracle External Table 详解

2026.09.06

Oracle External Table 详解

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

1. 概述

External Table 允许查询外部文件[1]：

详细见：Oracle 数据文件与表空间架构。

2. 类型

2.1 ORACLE_LOADER

- 文本文件
- 类似 SQL*Loader
- 只读

2.2 ORACLE_DATAPUMP

- Data Pump 格式
- 读写
- 高速

2.3 ORACLE_HDFS / ORACLE_HIVE

- Hadoop
- Big Data SQL
- 集成

3. 创建

3.1 目录

CREATE DIRECTORY ext_dir AS '/u01/ext_data';
GRANT READ, WRITE ON DIRECTORY ext_dir TO scott;

3.2 ORACLE_LOADER

CREATE TABLE ext_emp (
  emp_id NUMBER,
  emp_name VARCHAR2(100),
  salary NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('emp.csv')
)
REJECT LIMIT UNLIMITED;

3.3 ORACLE_DATAPUMP

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

4. 查询

4.1 SELECT

SELECT * FROM ext_emp WHERE salary > 5000;

4.2 性能

- 全表扫描
- 无索引
- 适合批量

4.3 限制

- 只读（ORACLE_LOADER）
- 不能 DML
- 不能索引

5. 加载

5.1 外部到内部

INSERT INTO emp 
SELECT * FROM ext_emp;

5.2 并行

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL */ INTO emp 
SELECT /*+ PARALLEL */ * FROM ext_emp;

5.3 性能

- 批量加载
- 并行
- 快

6. 导出

6.1 Data Pump

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

6.2 多文件

LOCATION ('exp1.dmp', 'exp2.dmp', 'exp3.dmp')

7. 查看日志

7.1 日志文件

- ext.log
- ext.bad
- ext.dsc

7.2 查看

SELECT * FROM all_directories;

8. 应用场景

8.1 ETL

- 加载外部数据
- 转换
- 批量

8.2 数据交换

- CSV 导入
- Data Pump 导出
- 跨库

8.3 归档

- 历史数据
- 外部文件
- 查询

9. 性能

9.1 并行

- PARALLEL
- 大文件
- 加速

9.2 分区

- 多文件
- LOCATION
- 并行

9.3 监控

SELECT * FROM v$session_longops WHERE ...;

10. 限制

10.1 DML

- ORACLE_LOADER：只读
- ORACLE_DATAPUMP：创建时写
- 不支持 UPDATE/DELETE

10.2 索引

- 不支持
- 全表扫描

10.3 约束

- 不支持
- 检查

11. 管理

11.1 修改

ALTER TABLE ext_emp LOCATION ('emp2.csv');
ALTER TABLE ext_emp DEFAULT DIRECTORY ext_dir2;

11.2 查看

SELECT table_name, type_name, default_directory_name
FROM user_external_tables;

12. 常见问题

12.1 ORA-29913

- 文件不存在
- 权限
- 路径

12.2 拒绝行

- 格式错误
- bad 文件
- 检查

12.3 性能

- 并行
- 大文件
- 优化

13. 最佳实践

  1. 目录：专用
  2. 权限：最小
  3. 并行：大文件
  4. Data Pump：高速
  5. 监控：拒绝
  6. 日志：检查
  7. ETL：使用
  8. 文档：格式
  9. 测试：加载
  10. 演练：定期

14. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “External Tables” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/