Oracle 临时表与临时段优化

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

1. 概述

临时表与临时段优化[1]：

类型：

临时表（Temporary Table）
排序段
临时 LOB

2. 临时表

2.1 创建

-- 事务级
CREATE GLOBAL TEMPORARY TABLE temp_data (
  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 特点

数据会话私有
自动清理
不写 Redo
写少量 Undo

2.3 索引

CREATE GLOBAL TEMPORARY TABLE temp_data (...) ON COMMIT DELETE ROWS;
CREATE INDEX idx_temp ON temp_data(id);

3. 临时表优化

3.1 适用场景

中间结果
复杂报表
临时存储

3.2 性能

-- 1. 大量 INSERT
INSERT INTO temp_data SELECT * FROM source;

-- 2. 后续查询
SELECT * FROM temp_data WHERE ...;

3.3 统计信息

-- 收集
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => USER,
  tabname => 'TEMP_DATA',
  stmtid => 'temp_stats'
);

4. 临时段

4.1 排序段

-- 查看
SELECT * FROM v$sort_segment;

-- 使用
SELECT 
  username, 
  sql_id, 
  blocks * 8 / 1024 AS mb
FROM v$sort_usage
ORDER BY blocks DESC;

4.2 临时段类型

SORT
HASH
BITMAP_MERGE
BITMAP_CREATE
LOB

5. 临时表空间

5.1 查看

SELECT 
  file_name, 
  bytes / 1024 / 1024 AS mb, 
  autoextensible
FROM dba_temp_files;

5.2 大小

-- 增大
ALTER TABLESPACE temp ADD TEMPFILE '/u02/temp02.dbf' SIZE 10G;

-- 自动扩展
ALTER DATABASE TEMPFILE '...' AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

5.3 多临时文件

ALTER TABLESPACE temp ADD TEMPFILE '/u02/temp02.dbf' SIZE 10G;
ALTER TABLESPACE temp ADD TEMPFILE '/u03/temp03.dbf' SIZE 10G;

6. 临时表空间组

6.1 创建

-- 组
ALTER TABLESPACE temp TABLESPACE GROUP temp_group;

-- 添加
ALTER TABLESPACE temp2 TABLESPACE GROUP temp_group;

6.2 使用

-- 用户
ALTER USER scott TEMPORARY TABLESPACE temp_group;

-- 默认
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;

6.3 优势

多表空间负载均衡
避免单文件满

7. 排序优化

7.1 PGA

ALTER SYSTEM SET pga_aggregate_target = 8G;
ALTER SYSTEM SET pga_aggregate_limit = 16G;

详细见：Oracle PGA 与排序优化。

7.2 减少排序

-- 1. 索引
CREATE INDEX idx_emp_sal ON employees(salary);

-- 2. UNION ALL 替代 UNION
SELECT id FROM a UNION ALL SELECT id FROM b;

-- 3. 减少 ORDER BY

7.3 并行排序

SELECT /*+ PARALLEL(8) */ * FROM big_table ORDER BY col;

8. 临时 LOB

8.1 创建

DECLARE
  v_lob CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_lob, TRUE);
  DBMS_LOB.WRITEAPPEND(v_lob, 100, '...');
  
  -- 使用
  
  DBMS_LOB.FREETEMPORARY(v_lob);
END;
/

8.2 优化

及时释放
避免大量临时 LOB

8.3 监控

SELECT * FROM v$temporary_lobs;

9. 监控

9.1 临时表空间使用

SELECT 
  tablespace_name,
  SUM(bytes_used) / 1024 / 1024 AS used_mb,
  SUM(bytes_free) / 1024 / 1024 AS free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;

9.2 排序使用

SELECT 
  username,
  session_addr,
  sql_id,
  blocks * 8 / 1024 AS mb,
  segtype
FROM v$sort_usage
ORDER BY blocks DESC;

9.3 活跃排序

SELECT 
  s.sid,
  s.serial#,
  s.username,
  w.operation,
  w.policy,
  w.estimated_optimal_size / 1024 AS est_kb,
  w.last_memory_used / 1024 AS used_kb
FROM v$session s, v$sql_workarea_active w
WHERE s.sid = w.sid;

10. 常见坑与排错

10.1 ORA-01652

-- 临时表空间满
-- 1. 增大
ALTER TABLESPACE temp ADD TEMPFILE '...' SIZE 5G;

-- 2. 找大用户
SELECT * FROM v$sort_usage ORDER BY blocks DESC;

10.2 排序慢

-- 1. 增大 PGA
-- 2. 加索引
-- 3. 并行

10.3 临时 LOB 泄漏

-- 1. 监控 v$temporary_lobs
-- 2. 应用释放
DBMS_LOB.FREETEMPORARY(...);

11. 最佳实践

临时表会话级：业务
PGA 充足：内存排序
临时表空间足够：避免满
多临时文件：I/O 均衡
表空间组：扩展
减少排序：索引
UNION ALL：避免排序
释放临时 LOB：避免泄漏
监控使用：及时
并行大排序：性能

12. 参考资料

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