面试宝典:介绍下Oracle数据库动态性能视图 V$TEMPFILE

V$TEMPFILE 是 Oracle 数据库中一个非常重要的动态性能视图，它记录了当前数据库中所有临时文件（tempfile）的信息。临时文件是临时表空间的物理存储载体，主要用于磁盘排序、哈希连接等需要临时存储空间的操作。下面我将详细介绍这个视图。

📊 V$TEMPFILE 动态性能视图详解

1. 概述与作用

V$TEMPFILE 视图提供了关于数据库临时文件的详细信息，这些临时文件属于临时表空间，主要用于存储排序操作（如 ORDER BY、GROUP BY）、哈希连接、临时表数据等产生的临时数据。它与存储永久数据的数据文件（datafile）不同，临时文件中的数据是会话特有的，并在会话结束或操作完成后被自动回收。

这个视图的主要作用包括：

• 监控临时空间：查看临时文件的大小、位置和状态。
• 空间管理：评估临时空间的使用情况，决定是否需要扩容或清理。
• 故障排查：当临时空间不足导致操作失败时，用于定位问题。
• 性能优化：了解哪些操作或会话占用了大量临时空间，从而进行优化。

2. 字段含义详解

下表详细列出了 V$TEMPFILE 中各字段的含义：

字段名	类型	是否可为空？	描述
FILE#	NUMBER	NOT NULL	在数据库内唯一的绝对文件号 (Absolute File Number)。注意这与某些内部映射有关。
CREATION_CHANGE#	NUMBER		创建该临时文件时的系统变更号（SCN）。
CREATION_TIME	DATE		临时文件的创建时间。
TS#	NUMBER	NOT NULL	该临时文件所属的表空间号 (Tablespace Number)。
RFILE#	NUMBER	NOT NULL	在所属表空间内的相对文件号 (Relative File Number)。
STATUS	VARCHAR2(7)		文件状态：ONLINE（在线）、OFFLINE（离线）或 UNKNOWN（未知）。
ENABLED	VARCHAR2(10)		文件的访问模式：READ WRITE（读写）、READ ONLY（只读）或 DISABLED（禁用）。临时文件通常应为 READ WRITE。
BYTES	NUMBER		文件的当前大小（以字节为单位）。
BLOCKS	NUMBER		文件的当前大小（以Oracle块为单位）。
CREATE_BYTES	NUMBER		文件最初创建时的大小（以字节为单位）。
BLOCK_SIZE	NUMBER		文件的块大小（以字节为单位）。通常与数据库标准块大小一致。
NAME	VARCHAR2(513)		临时文件的完整路径名。
CON_ID	NUMBER		在多租户环境中，该临时文件所属的容器ID (Container ID)。对于非CDB数据库，此值通常为0。

注意事项

• FILE# (绝对文件号) 的特殊性：临时文件的绝对文件号分配规则与普通数据文件不同。其值可能与底层基表 X$KCCTF 中的 TFAFN (Temp File Absolute File Number) 字段存在映射关系，并且有时会与 DB_FILES 参数值有关。
• SIZE 与 SPACE：临时文件在创建时通常被初始化为“稀疏文件”（Sparse File），磁盘空间并非立即全部分配，而是按需分配。

3. 相关视图与基表

V$TEMPFILE 并非基表，而是基于更底层的内存结构构建的动态性能视图。

3.1 相关动态性能视图

• V$TEMPFILE: 本视图。
• DBA_TEMP_FILES: 数据字典视图，也提供类似信息，但角度略有不同，更侧重于存储属性。
• V$TEMP_SPACE_HEADER: 显示每个临时文件的已使用和空闲空间情况，对于实时监控特定临时文件的空间消耗非常有用。
• V$SOR{T}_{U}SAGE\ast \ast /\ast \ast V$TEMPSEG_USAGE: 这两个视图通常是等价的同义词，用于查看当前是哪些会话（SESSION_ADDR）、SQL语句（SQL_ID）或操作（OPERATION）正在使用临时空间，以及使用了多少空间（BLOCKS）。这对于定位高临时空间消耗的源头至关重要。
• V$TEMP_EXTENT_POOL: 显示实例缓存和使用的临时空间状态。
• V$TEMP_EXTENT_MAP: 显示本地管理的临时表空间中所有区的信息。
• DBA_FREE_SPACE: 此视图不适用于临时表空间。临时表空间的空闲空间管理机制与永久表空间不同。

3.2 底层基表与原理

• **X$KCCTF\ast \ast :这是‘V$TEMPFILE和GV$TEMPFILE‘视图所基于的\ast \ast 核心底层内存结构\ast \ast （可近似理解为基表）。它存储了关于临时文件的详细内部信息，例如‘TFNUM‘(对应V$TEMPFILE的FILE#), TFAFN (绝对文件号), TFCSZ (创建时的大小，以块为单位)等。
• 稀疏文件与空间分配: 临时文件（Tempfile）在操作系统层面通常创建为“稀疏文件”。这意味着Oracle在创建文件时并不会立即分配全部的磁盘空间，而是随着排序、哈希等操作需要临时空间时，再动态地向文件中分配区（Extent）。这节省了初始磁盘空间，但也意味着需要监控文件系统的剩余空间，因为临时文件的最大扩展大小可能超出你的预期。
• 排序机制: 当处理大规模排序（ORDER BY）、分组（GROUP BY）、连接或全局临时表数据时，如果工作区（Work Area）在PGA中放不下，Oracle就会将中间结果写入临时表空间的临时段（Temporary Segment）中。这些临时段就存放在临时文件里。操作完成后，这些段会被标记为可释放，但磁盘空间通常不会被立即回收回操作系统，而是由Oracle管理以便重用。

4. 常见使用场景

1. 监控临时空间总体使用情况：定期检查临时文件的总大小和空闲空间，预防空间不足错误。
2. 诊断空间不足错误：当遇到 ORA-01652: unable to extend temp segment 错误时，使用 V$TEMPFILE 和 V$SORT_USAGE 来定位是哪个文件满了，以及是哪个操作或会话导致的。
3. 调整临时表空间大小：根据监控结果，决定是否需要为临时表空间添加文件、调整文件大小或设置为自动扩展。
4. 管理临时文件：在存储迁移或重构时，可能需要添加、移动或删除临时文件。

5. 常用SQL查询示例

5.1 查看所有临时文件的基本信息

SELECT file#,
       name,
       bytes / 1024 / 1024 AS size_mb,
       status,
       enabled
FROM v$tempfile;

5.2 查询临时表空间及其文件的详细使用情况

这个查询结合了 V$TEMP_SPACE_HEADER（实时使用）和 DBA_TEMP_FILES（配置大小），提供了使用百分比，非常直观。

SELECT d.tablespace_name,
       d.file_name,
       d.bytes / 1024 / 1024 allocated_mb,
       (d.bytes - NVL(s.bytes_used, 0)) / 1024 / 1024 free_mb,
       NVL(s.bytes_used, 0) / 1024 / 1024 used_mb,
       ROUND(NVL(s.bytes_used, 0) * 100 / d.bytes, 2) pct_used
FROM dba_temp_files d
LEFT JOIN (SELECT file_id, SUM(bytes_used) bytes_used
           FROM v$temp_space_header
           GROUP BY file_id) s ON d.file_id = s.file_id
ORDER BY d.tablespace_name, d.file_id;

5.3 查找正在使用临时空间的会话和SQL

当临时空间激增时，此查询能帮你快速定位元凶。

SELECT su.username,
       su.session_addr,
       su.sql_id,
       su.contents,
       su.segtype,
       su.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024 size_used_mb,
       sq.sql_text
FROM v$sort_usage su
JOIN v$sql sq ON su.sql_id = sq.sql_id
ORDER BY su.blocks DESC;

5.4 检查临时文件是否设置为自动扩展

SELECT file_name, autoextensible, increment_by, maxbytes
FROM dba_temp_files;

5.5 为临时表空间添加新文件

如果现有临时文件已满且不便扩大，可以添加新文件。

ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/ORCL/temp02.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

5.6 调整现有临时文件的大小

ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 10G;

5.7 删除临时文件（确保未被使用）

-- 首先确认文件是否正在被使用（SEGFILE# 对应 V$TEMPFILE 中的 FILE#）
SELECT * FROM v$sort_usage WHERE segfile# = &target_file_number;

-- 如果无返回结果，则可以删除
ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp_old.dbf' DROP INCLUDING DATAFILES;

✍️ 总结

Oracle 中的 V$TEMPFILE 动态性能视图是管理临时表空间物理文件的核心工具。通过它，DBA 可以监控临时空间的分配、识别潜在的空间压力、并执行必要的管理操作如扩容或清理。理解其字段含义、并结合 V$SORT_USAGE 等其他相关视图，是有效管理 Oracle 数据库临时空间、确保数据库稳定高效运行的关键。

希望以上详细的解释能帮助你更好地理解和使用 V$TEMPFILE 视图。

欢迎关注我的公众号《IT小Chen》