DB / Oracle / SQL 开发

Oracle 临时表与外部表

2026.09.06

Oracle 临时表与外部表

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

1. 概述

临时表与外部表[1]：

临时表：会话/事务级 外部表：文件作为表

2. 临时表

2.1 创建

-- 事务级
CREATE GLOBAL TEMPORARY TABLE temp_emp (
  id NUMBER,
  name VARCHAR2(100)
) ON COMMIT DELETE ROWS;

-- 会话级
CREATE GLOBAL TEMPORARY TABLE temp_session (
  id NUMBER,
  name VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;

2.2 使用

INSERT INTO temp_emp VALUES (1, 'Alice');
INSERT INTO temp_emp VALUES (2, 'Bob');

SELECT * FROM temp_emp;
-- 会话内可见

2.3 索引

CREATE INDEX idx_temp_emp ON temp_emp(id);
-- 索引也是临时的

2.4 删除

TRUNCATE TABLE temp_emp;
DROP TABLE temp_emp;

3. 临时表特性

3.1 数据可见性

- 仅当前会话可见
- 事务级：COMMIT 删除
- 会话级：会话结束删除

3.2 存储

- 定义在数据字典
- 数据在临时段（TEMP）
- 不计入表配额

3.3 性能

- 无 REDO（DML 产生 UNDO）
- 少量日志
- 快速

4. 12c+ 私有临时表

4.1 创建

-- 仅内存
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_emp (
  id NUMBER,
  name VARCHAR2(100)
) ON COMMIT DROP DEFINITION;

-- ON COMMIT PRESERVE DEFINITION

4.2 特性

- 内存
- 会话或事务
- 前缀 ORA$PTT_

5. 外部表

5.1 创建（ORACLE_LOADER）

CREATE DIRECTORY ext_data AS '/u01/data';

CREATE TABLE ext_employees (
  id NUMBER,
  name VARCHAR2(100),
  salary NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

5.2 查询

SELECT * FROM ext_employees WHERE salary > 5000;

5.3 加载数据

-- INSERT ... SELECT
INSERT INTO employees SELECT * FROM ext_employees;

详细见：Oracle 数据加载工具对比。

6. ORACLE_DATAPUMP

6.1 写入

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

6.2 读取

-- 另一个数据库
CREATE TABLE ext_import (...) 
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY ext_data
  LOCATION ('export.dmp')
);

7. 外部表分区

7.1 创建

CREATE TABLE ext_sales (
  sale_date DATE,
  amount NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data
  ACCESS PARAMETERS (...)
  LOCATION ('sales_2024.csv')
)
REJECT LIMIT UNLIMITED
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

8. 外部表参数

8.1 字段分隔

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

8.2 日期格式

date1 DATE "YYYY-MM-DD HH24:MI:SS"

8.3 拒绝

REJECT LIMIT UNLIMITED
REJECT LIMIT 100

8.4 日志

LOGFILE 'ext.log'
BADFILE 'ext.bad'
DISCARDFILE 'ext.dis'

9. 视图与限制

9.1 查看

SELECT table_name, type_name 
FROM user_external_tables;

9.2 限制

- 只读
- 不能 DML
- 索引受限
- 性能依赖 I/O

10. 性能

10.1 并行

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

10.2 内存

-- 增大 PGA
ALTER SYSTEM SET pga_aggregate_target = 4G;

10.3 文件分布

- 多文件并行
- 不同磁盘
- 减少 I/O 等待

11. 应用场景

11.1 临时表

  • 复杂计算中间结果
  • 会话级临时数据
  • 批处理中间表

11.2 外部表

  • CSV 加载
  • 数据迁移
  • ETL
  • 报表

12. 常见坑与排错

12.1 临时表数据丢失

- 事务级：COMMIT 后删除
- 改 ON COMMIT PRESERVE ROWS

12.2 外部表 ORA-29913

-- 1. 目录权限
GRANT READ, WRITE ON DIRECTORY ext_data TO scott;

-- 2. 文件存在
-- 3. 格式正确

12.3 ORA-30653

-- 拒绝数超限
-- 检查数据格式
-- 增大 REJECT LIMIT

13. 最佳实践

  1. 事务级临时表：批量处理
  2. 会话级临时表：会话数据
  3. 私有临时表：12c+
  4. 外部表加载：ETL
  5. ORACLE_DATAPUMP：跨库
  6. 并行外部表：性能
  7. 目录权限：必要
  8. 日志监控：诊断
  9. 格式准确：成功
  10. 文档化：使用

14. 参考资料

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