Oracle临时表空间管理维护总结

Oracle临时表空间管理维护总结

一、临时表空间概述

1.1 什么是临时表空间

临时表空间（Temporary Tablespace）是Oracle数据库中用于存储会话级临时数据的核心组件。它主要用于支持需要中间结果集的操作，如排序、哈希连接等，当这些操作所需的内存（PGA）不足时，Oracle会自动将数据写入临时表空间。

临时表空间包含仅在会话期间持续存在的临时数据，可以提高无法装入内存的多个排序操作的并发性。它作为数据库的重要组成部分，尤其是在大型的频繁操作（如创建索引、排序等）中，用于在磁盘上完成操作以减少内存开销。

与永久表空间不同，临时表空间不存储任何持久化数据，不产生Redo日志，无需纳入备份恢复体系。

1.2 临时表空间的核心特点

1.3 临时表空间存储的数据类型

临时表空间主要用于存储以下内容：

1.4 临时表空间的主要作用

临时表空间的主要作用是支持以下需要临时存储的操作：

• 创建或重建索引（CREATE INDEX、ALTER INDEX … REBUILD）
• ORDER BY 或 GROUP BY 排序
• DISTINCT 去重操作
• UNION、INTERSECT、MINUS 集合操作
• Sort-Merge Joins（排序合并连接）
• ANALYZE 分析操作

1.5 数据生命周期管理

创建时机：

• 当操作所需内存（PGA）不足时，Oracle自动将数据写入临时表空间
• 用户显式创建全局临时表（GTT）时

释放机制：

• 事务级临时数据：事务提交（COMMIT）或回滚（ROLLBACK）后释放
• 会话级临时数据：会话终止（用户断开连接）后释放
• 显式清理：可通过ALTER TABLESPACE temp SHRINK SPACE手动回收空间

1.6 临时表空间文件的稀疏文件特性（延迟分配空间）

不管多大的临时文件，我们在创建时，都可以瞬间完成，其核心奥秘在于它采用了一种名为 “稀疏文件”（Sparse File） 的特殊文件类型，这是一种“延迟分配”技术，它并非传统意义上的“创建”，更像是在操作系统层面进行了一次“虚拟注册”。

普通数据文件 vs. 临时文件
两者的核心区别在于创建时的工作负载完全不同：

按需分配：当数据库首次向该临时文件写入数据时（例如执行排序操作），操作系统才会根据需要，为写入的数据块分配真正的物理磁盘空间。

特别注意：需要注意底层操作系统上真的有足够的物理空间，否则会出现ORA-01652: unable to extend temp segment无法分配临时段空间的错误

二、临时表空间的特性与注意事项

2.1 核心特性

• 用户存储临时数据的表空间，临时数据通常只在一个数据库会话期间存在
• 临时数据不会被写入存储永久对象的普通表空间，而是存储在临时表空间的临时段中
• 对于临时数据的处理，不会生成重做（Redo），也不会生成撤销（Undo）数据
• 临时表空间的数据文件不能置为只读、不能重命名

2.2 使用注意事项

2.3 临时表空间 vs 永久表空间

三、临时表空间创建

3.1 基本语法

使用CREATE TEMPORARY TABLESPACE语句创建本地管理的临时表空间：

CREATE TEMPORARY TABLESPACE tablespace_name
    TEMPFILE 'tempfile_path' SIZE size
    [AUTOEXTEND ON NEXT increment_size MAXSIZE max_size]
    [EXTENT MANAGEMENT LOCAL UNIFORM SIZE size];

3.2 创建示例

-- 创建基础临时表空间（50M）
CREATE TEMPORARY TABLESPACE temp_ts 
TEMPFILE '/oradata/orcl/temp_ts01.dbf' SIZE 50M;

-- 创建带自动扩展的临时表空间
CREATE TEMPORARY TABLESPACE temp_data 
TEMPFILE '/oradata/orcl/temp_data01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- 指定区管理方式（本地管理，统一区大小）
CREATE TEMPORARY TABLESPACE temp_large 
TEMPFILE '/oradata/orcl/temp_large01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

注意：必须使用CREATE TEMPORARY TABLESPACE语句来创建本地管理的临时表空间。

3.3 创建大文件临时表空间

CREATE BIGFILE TEMPORARY TABLESPACE temp_big 
TEMPFILE '/oradata/orcl/temp_big01.dbf' SIZE 10G;

3.4 使用OMF创建临时表空间

启用OMF后，可以简化创建：

-- 设置OMF临时文件路径
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/oradata/orcl';

-- 创建临时表空间（Oracle自动命名）
CREATE TEMPORARY TABLESPACE temp_omf SIZE 50M;

四、临时表空间扩容

4.1 方法一：添加新的临时文件

ALTER TABLESPACE tablespace_name ADD TEMPFILE 'tempfile_path' SIZE size;

-- 示例
ALTER TABLESPACE temp ADD TEMPFILE '/oradata/orcl/temp02.dbf' SIZE 1G;

4.2 方法二：调整现有临时文件大小

ALTER DATABASE TEMPFILE 'tempfile_path' RESIZE new_size;

-- 示例
ALTER DATABASE TEMPFILE '/oradata/orcl/temp01.dbf' RESIZE 2G;

4.3 方法三：启用自动扩展

-- 对已存在的临时文件启用自动扩展
ALTER DATABASE TEMPFILE 'tempfile_path' 
AUTOEXTEND ON NEXT increment_size MAXSIZE max_size;

-- 示例
ALTER DATABASE TEMPFILE '/oradata/orcl/temp01.dbf' 
AUTOEXTEND ON NEXT 200M MAXSIZE 20G;

五、临时表空间切换

5.1 切换数据库默认临时表空间

-- 切换默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE new_temp_tbs;

5.2 切换用户的默认临时表空间

-- 修改指定用户的默认临时表空间
ALTER USER username TEMPORARY TABLESPACE new_temp_tbs;

六、临时表空间删除

6.1 基本语法

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

6.2 删除步骤

-- 1. 先切换到其他临时表空间（如果是默认临时表空间）
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

-- 2. 确认没有用户在使用要删除的临时表空间
SELECT username, temporary_tablespace FROM dba_users 
WHERE temporary_tablespace = 'TEMP_OLD';

如果有，切换用户的默认临时表空间
ALTER USER username TEMPORARY TABLESPACE new_temp_tbs;

-- 3. 删除临时表空间
DROP TABLESPACE temp_old INCLUDING CONTENTS AND DATAFILES;

七、临时表空间组（Tablespace Group）

7.1 概述

临时表空间组（Temporary Tablespace Group）是Oracle 10g引入的新特性，允许将多个临时表空间打包成一个组，然后指定用户的默认临时表空间为该组，从而达到一个用户可以使用多个临时表空间的目的。

在Oracle 10g之前，同一用户的多个会话只可以使用同一个临时表空间，这成为潜在的瓶颈。临时表空间组解决了这个问题，使得并行执行服务器可以在单个并行操作中使用多个临时表空间。

7.2 临时表空间组的特性

7.3 创建和使用临时表空间组

-- 创建临时表空间时直接指定组
CREATE TEMPORARY TABLESPACE temp_group_member1
  TEMPFILE '/oradata/orcl/temp_grp1.dbf' SIZE 50M
  TABLESPACE GROUP temp_grp;

-- 将已有临时表空间加入组
ALTER TABLESPACE temp_data TABLESPACE GROUP temp_grp;

-- 将数据库默认临时表空间设置为临时表空间组
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;

-- 将用户临时表空间设置为临时表空间组
ALTER USER scott TEMPORARY TABLESPACE temp_grp;

-- 查看临时表空间组的成员
SELECT * FROM dba_tablespace_groups;

-- 将临时表空间从组中移除
ALTER TABLESPACE temp_data TABLESPACE GROUP '';

-- 删除临时表空间组（移除所有成员后自动删除）
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
ALTER USER scott TEMPORARY TABLESPACE temp;
DROP TABLESPACE temp_group_member1 INCLUDING CONTENTS AND DATAFILES;

八、如何评估所需临时表空间大小

临时表空间的容量规划应基于业务高峰期的峰值使用量，推荐评估步骤如下：

SELECT sample_id,SUM(temp_space_allocated) / 1024 / 1024 / 1024 AS current_temp_gb
FROM v$active_session_history
WHERE temp_space_allocated > 0
group by sample_id
order by 2 desc;

备注：临时表空间的总容量应设置为历史峰值使用量的1.5~2倍，预留缓冲空间应对突发负载

九、临时表空间监控与查询

9.1 常用的数据字典和视图

9.2 查看临时表空间基本信息

-- 查看所有临时表空间
SELECT tablespace_name, status, contents 
FROM dba_tablespaces 
WHERE contents = 'TEMPORARY';

-- 查看临时表空间文件
set linesize 200 pagesize 999
col file_name format a40
SELECT tablespace_name, file_name, 
       ROUND(bytes / 1024 / 1024, 0) AS size_mb,
       autoextensible, maxbytes
FROM dba_temp_files;

-- 使用V$TEMPFILE查看（SYS用户）
set linesize 200 pagesize 999
col name format a40
SELECT status, enabled, name, 
       ROUND(bytes / 1024 / 1024, 0) AS size_mb
FROM v$tempfile;

9.3 查看临时表空间使用率

-- 方法一：使用DBA_TEMP_FREE_SPACE（Oracle 11g+）
SELECT tablespace_name, 
       ROUND(tablespace_size / 1024 / 1024, 0) AS total_mb,
       ROUND(allocated_space / 1024 / 1024, 0) AS allocated_mb,
       ROUND(free_space / 1024 / 1024, 0) AS free_mb
FROM dba_temp_free_space;

-- 方法二：查询临时表空间真实使用率（注意：临时表空间不使用dba_free_space）
SELECT a.tablespace_name,
       ROUND(a.total_mb, 2) AS total_mb,
       ROUND(b.used_mb, 2) AS used_mb,
       ROUND(a.total_mb - b.used_mb, 2) AS free_mb,
       ROUND(b.used_mb / a.total_mb * 100, 2) AS used_pct
FROM
  (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
   FROM dba_temp_files
   GROUP BY tablespace_name) a,
  (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 AS used_mb
   FROM v$temp_space_header
   GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);


-- 方法三：当前正在使用的TEMP表空间的单节点使用情况
SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;
 
--  当前正在使用的TEMP表空间总的使用情况
SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM gv$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;

9.4 查看当前占用临时表空间的会话

--会话使用的 TEMP 段
SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       S.osuser,
       P.spid,
       S.module,
       S.program,
       s.sql_id,
       SUM(T.blocks) * TBS.block_size / 1024 /1024 mb_used,
       T.tablespace,
       COUNT(*) sort_ops
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
   AND S.paddr = P.addr
   AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
          S.serial#,
          S.username,
          S.osuser,
          P.spid,
          S.module,
          S.program,
          s.sql_id,
          TBS.block_size,
          T.tablespace
 ORDER BY sid_serial;

--语句使用的临时空间
SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       T.blocks * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       T.sqladdr address,
       Q.hash_value,
       Q.sql_text
  FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
 WHERE T.session_addr = S.saddr
   AND T.sqladdr = Q.address(+)
   AND T.tablespace = TBS.tablespace_name
 ORDER BY S.sid;
 
 
-- 查看当前正在使用临时表空间的会话
SELECT s.sid, s.serial#, s.username, 
       u.tablespace AS tablespace_name, 
       u.contents, u.segtype, 
       ROUND(u.blocks * 8192 / 1024 / 1024, 2) AS used_mb
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr;

-- 查找占用临时表空间最多的会话
SELECT s.sid, s.serial#, p.spid, s.username, s.program,
       ROUND(SUM(t.blocks) * 8192 / 1024 / 1024, 2) AS mb_used
FROM v$sort_usage t, v$session s, v$process p
WHERE s.saddr = t.session_addr AND s.paddr = p.addr
GROUP BY s.sid, s.serial#, p.spid, s.username, s.program
ORDER BY mb_used DESC;

9.5 查看用户的默认临时表空间

-- 查看所有用户的默认临时表空间
SELECT username, temporary_tablespace 
FROM dba_users;

-- 查看数据库默认临时表空间
SELECT property_name, property_value 
FROM database_properties 
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

十、释放临时表空间

10.1 临时表空间不释放的原因

临时表空间使用率过高是常见问题。重启数据库可以释放临时表空间，但如果不能重启实例，而一直保持问题SQL语句的执行，临时表空间会一直增长直到耗尽磁盘空间。

临时表空间当前占用的空间并不一定是由会话当前正在执行的SQL所产生的。临时表空间在正常运行的数据库中，经过一段时间后会看起来“已满”——区段被分配一次后由系统管理。

10.2 Oracle 11g+ SHRINK功能

从Oracle 11g开始，可以使用收缩命令在线回收临时表空间：

-- 收缩整个临时表空间
ALTER TABLESPACE temp SHRINK SPACE;

-- 收缩临时表空间并保留指定大小
ALTER TABLESPACE temp SHRINK SPACE KEEP 10G;

-- 收缩指定的临时文件
ALTER TABLESPACE temp SHRINK TEMPFILE '/oradata/orcl/temp01.dbf';

-- 收缩临时文件并保留指定大小
ALTER TABLESPACE temp SHRINK TEMPFILE '/oradata/orcl/temp01.dbf' KEEP 60M;

10.3 重建临时表空间（彻底释放）

如果收缩无法有效释放空间，可以通过重建方式彻底释放：

-- 1. 创建新的临时表空间
CREATE TEMPORARY TABLESPACE temp_new 
TEMPFILE '/oradata/orcl/temp_new01.dbf' SIZE 1G;

-- 2. 切换默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

-- 3. 修改所有用户的临时表空间
SELECT 'ALTER USER ' || username || ' TEMPORARY TABLESPACE temp_new;'
FROM dba_users 
WHERE temporary_tablespace = 'TEMP';

-- 4. 删除旧的临时表空间
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

-- 5. （可选）重新创建原名称的临时表空间并切换回来

10.4 终止占用临时表空间的会话

如果发现某个会话占用大量临时表空间，可以终止该会话：

-- 终止会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

十一、临时表空间常见错误

11.1 ORA-01652：无法扩展临时段

错误信息：

ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

原因：临时表空间已满或无法自动扩展

解决方法：

-- 方法一：添加临时文件
ALTER TABLESPACE temp ADD TEMPFILE '/path/temp02.dbf' SIZE 1G;

-- 方法二：调整现有临时文件大小
ALTER DATABASE TEMPFILE '/path/temp01.dbf' RESIZE 2G;

-- 方法三：启用自动扩展
ALTER DATABASE TEMPFILE '/path/temp01.dbf' AUTOEXTEND ON NEXT 200M MAXSIZE 20G;

-- 方法四：优化SQL减少磁盘排序
-- 增大PGA_AGGREGATE_TARGET参数
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G SCOPE=BOTH;

11.2 ORA-25153：临时表空间为空

错误信息：

ORA-25153: Temporary Tablespace is Empty

原因：临时表空间中没有临时文件

解决方法：

ALTER TABLESPACE temp ADD TEMPFILE '/path/temp01.dbf' SIZE 500M;

11.3 临时表空间使用率过高

现象：临时表空间使用率超过90%

解决方法：

1. 查找并终止占用大量临时空间的会话
2. 扩容临时表空间大小
3. 优化SQL语句，减少排序和哈希操作
4. 增加PGA内存以减少磁盘排序
5. 如需要，重建临时表空间

11.4 临时文件碎片化

原因：频繁分配和释放临时段

解决方法：

• 定期重建临时表空间
• 使用ALTER TABLESPACE temp SHRINK SPACE进行整理

11.5 临时表空间损坏

说明：临时表空间损坏后无法用RMAN恢复，因其不存持久化数据且RMAN不备份

解决方法：重建临时表空间并切换默认

-- 创建新的临时表空间
CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/path/temp_new01.dbf' SIZE 1G;

-- 切换默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

-- 删除损坏的临时表空间
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;