Oracle 数据库外部表详解

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

1. 概述

外部表允许查询文件数据[1]：

2. ORACLE_LOADER

2.1 目录

CREATE DIRECTORY data_dir AS '/u01/data';
CREATE DIRECTORY log_dir AS '/u01/log';
GRANT READ, WRITE ON DIRECTORY data_dir TO scott;
GRANT READ, WRITE ON DIRECTORY log_dir TO scott;

2.2 创建

CREATE TABLE ext_sales (
  id NUMBER,
  sale_date DATE,
  amount NUMBER,
  region VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE log_dir:'sales.bad'
    LOGFILE log_dir:'sales.log'
    DISCARDFILE log_dir:'sales.dsc'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
      id CHAR,
      sale_date CHAR DATE_FORMAT DATE MASK 'YYYY-MM-DD',
      amount CHAR,
      region CHAR
    )
  )
  LOCATION ('sales_2025.csv')
)
REJECT LIMIT UNLIMITED
PARALLEL 4;

2.3 多文件

LOCATION ('sales_2025_01.csv', 'sales_2025_02.csv', 'sales_2025_03.csv')

2.4 查询

SELECT * FROM ext_sales;
SELECT * FROM ext_sales WHERE region = 'East';

-- 加载
INSERT /*+ APPEND */ INTO sales SELECT * FROM ext_sales;

3. ORACLE_DATAPUMP

3.1 导出

CREATE TABLE exp_sales
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY data_dir
  LOCATION ('sales.dmp')
)
AS SELECT * FROM sales;

-- 生成 sales.dmp 文件

3.2 导入

-- 在另一数据库
CREATE TABLE imp_sales (
  id NUMBER,
  sale_date DATE,
  amount NUMBER,
  region VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY data_dir
  LOCATION ('sales.dmp')
);

SELECT * FROM imp_sales;

4. ORACLE_HDFS（Big Data SQL）

CREATE TABLE hdfs_sales (
  id NUMBER,
  amount NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_HDFS
  DEFAULT DIRECTORY hdfs_dir
  ACCESS PARAMETERS (
    com.oracle.bigdata.colmap: ...
    com.oracle.bigdata.overflow: ...
  )
  LOCATION ('/data/sales/*')
);

5. ORACLE_HIVE（Big Data SQL）

CREATE TABLE hive_sales
ORGANIZATION EXTERNAL (
  TYPE ORACLE_HIVE
  DEFAULT DIRECTORY hive_dir
  ACCESS PARAMETERS (
    com.oracle.bigdata.tableName: sales
  )
);

6. ACCESS PARAMETERS

6.1 字段分隔

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

6.2 记录分隔

RECORDS DELIMITED BY NEWLINE
RECORDS DELIMITED BY '\n'

6.3 日期

sale_date CHAR DATE_FORMAT DATE MASK 'YYYY-MM-DD'

6.4 转换

amount CHAR(10) "TO_NUMBER(:amount)"

6.5 拒绝

REJECT LIMIT UNLIMITED
REJECT LIMIT 100

7. 修改

7.1 LOCATION

ALTER TABLE ext_sales LOCATION ('sales_2025_07.csv');

7.2 DEFAULT DIRECTORY

ALTER TABLE ext_sales DEFAULT DIRECTORY new_data_dir;

7.3 ACCESS PARAMETERS

ALTER TABLE ext_sales 
  ACCESS PARAMETERS (FIELDS TERMINATED BY '|');

8. 视图

8.1 信息

SELECT table_name, type_name, default_directory_name
FROM user_external_tables;

8.2 位置

SELECT table_name, location
FROM user_external_locations;

9. 并行

CREATE TABLE ext_sales (...) 
  ORGANIZATION EXTERNAL (...) 
  PARALLEL 4;

ALTER TABLE ext_sales PARALLEL 8;

-- 并行查询
SELECT /*+ PARALLEL(e 4) */ * FROM ext_sales e;

10. 性能

10.1 文件

- 多文件并行
- 大文件分割
- 压缩

10.2 直接路径

INSERT /*+ APPEND */ INTO sales SELECT * FROM ext_sales;

详细见：Oracle SQL 查询优化技巧。

11. 应用场景

11.1 ETL 加载

-- 文件 → 外部表 → 仓库
INSERT /*+ APPEND */ INTO sales 
SELECT * FROM ext_sales;

详细见：Oracle 数据仓库 ETL 详解。

11.2 数据交换

- Data Pump 格式
- 跨平台
- 快速

11.3 大数据

- HDFS
- Hive
- Big Data SQL

11.4 报表

-- 直接查询文件
SELECT region, SUM(amount) FROM ext_sales GROUP BY region;

12. 错误处理

12.1 BADFILE

- 错误记录
- 检查
- 修正

12.2 LOGFILE

- 加载日志
- 警告
- 拒绝原因

12.3 DISCARDFILE

- 不满足条件
- 检查

12.4 REJECT LIMIT

- 错误上限
- UNLIMITED
- 控制

13. 常见坑与排错

13.1 ORA-29913

- ODCI 错误
- 目录权限
- 文件格式

13.2 ORA-29400

- 数据错误
- BADFILE
- 格式

13.3 权限

- DIRECTORY 读写
- OS 文件权限

13.4 字符集

- NLS
- 字符集
- 转换

14. 最佳实践

DIRECTORY：管理
PARALLEL：性能
APPEND：加载
多文件：并行
错误文件：监控
REJECT LIMIT：控制
Big Data：HDFS/Hive
Data Pump：交换
压缩：空间
测试：验证

15. 参考资料

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