【Oracle篇】伪列之Version Query：全链路追踪行数据变更的所有记录(不仅仅是被修改的最后时间)（第二篇，总共六篇）

💫《博主主页》：
　　　🔎 CSDN主页__奈斯DB
　　　🔎 IF Club社区主页__奈斯、
🔥《擅长领域》：擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控；并对SQLserver、NoSQL(Redis)有了解
💖如果觉得文章对你有所帮助，欢迎点赞收藏加关注💖

在这里插入图片描述
话接上文，在上文中了解了通过ORA_ROWSCN伪列可以精准定位行数据的最后修改时间⏰。但是如果有这样一个场景，不仅想知道行数据被修改的时间，还想知道修改前的旧值，以及这行数据被修改了多少次，需要进行数据追溯，那这个有没有办法实现的呢🔍？
Oracle表示："当然可以！"✨ 通过 闪回版本查询(Flashback Version Query)的伪列 就可以轻松实现：

VERSIONS_STARTTIME/VERSIONS_STARTSCN - 版本开始时间/SCN
VERSIONS_ENDTIME/VERSIONS_ENDSCN - 版本结束时间/SCN
VERSIONS_XID - 创建该版本的事务ID（追踪！🕵️♂️）
VERSIONS_OPERATION - 操作类型(I/U/D)🔧

不过需要注意的是⚠️ ，这个功能依赖 UNDO表空间 中的数据，必须在UNDO_RETENTION参数保留期内查询（默认通常15分钟~24小时），但实际保留时间还取决于UNDO表空间大小📊，那么下面开始版本查询伪列的介绍。
在Oracle 12c官方文档中可查的的伪列总共有 10个 ，对于网上的博客，以及其他学习资料对伪列的介绍都非常少，几乎没有大佬去讲关于伪列的内容，那么博主将用一个系列去认真介绍一下这 10个 伪列。先介绍一下伪列，伪列也叫虚拟列、特殊列、隐式列，不管怎么称呼，都泛指伪列。伪列是Oracle中一种特殊的列，它不像普通列那样存储在表中，但可像普通列一样在查询中进行引用，在查询时由数据库动态生成，主要用于获取行标识、序列值、数据操作相关元数据等特殊信息。需要注意的是伪列只能进行查询，不能插入、更新或删除它们的值。伪列类似于无参数的函数，但无参数函数通常对结果集中的每一行返回相同值，而伪列通常为每一行返回不同的值。对于比较常用的伪列将用独立的一篇文章介绍，对于不常用的伪列将用一篇文章简单介绍清楚其用途即可，因为在实际工作中有几个伪列使用的很频繁，真的非常有用处，关于伪列的系列文章如下：

第一篇：伪列之ORA_ROWSCN：精准查看行数据被修改的最后时间
第二篇：伪列之Version Query：全链路追踪行数据变更的所有记录(不仅仅是被修改的最后时间)（当前篇）
第三篇：伪列之Sequence：利用CURRVAL和NEXTVAL实现主键自增(含 12c 新特性通过 Identity 列实现主键自增)
第四篇：伪列之ROWID：快速查找数据行的物理地址
第五篇：伪列之ROWNUM：分页查询的实现
第六篇：伪列之Hierarchical Query（层次查询）、COLUMN_VALUE（列值提取）、OBJECT_ID（对象标识）、OBJECT_VALUE（对象值访问）、XMLDATA（XML原始数据）

特别说明💥：本篇文章部分理论性知识点均来源于版权归 Oracle 所有的官方公开文档手册，并结合了我个人的解读和案例演示。若需要调整，请联系，会尽快处理😄

官方文档对于版本查询伪列的介绍（Oracle 12c）：
Version Query Pseudocolumns

- 一、版本查询伪列介绍：
- 二、版本查询伪列 VS ORA_ROWSCN伪列
- - 案例一：创建表，针对多次被修改的某行数据通过版本查询伪列查看更改时间，以及修改前后的数据
  - 案例二：在现存表上使用版本查询伪列，针对多次被修改的某行数据通过版本查询伪列查看更改时间，以及修改前后的数据

一、版本查询伪列介绍：

    版本查询伪列仅在闪回版本查询（Flashback Version Query）中有效，闪回版本查询是闪回查询（Flashback Query）中的一种。
    既然版本查询伪列和闪回功能有关，那么先介绍一下Oracle中的闪回功能。Oracle中有多种闪回功能。包括Flashback Database（闪回数据库）、Flashback Drop（回收站）、Flashback Query（闪回查询）、flashback Table（闪回表）、Flashback Data Archive（闪回数据归档）。在Flashback Query（闪回查询）中又分为闪回查询（Flashback Query）、闪回版本查询（Flashback Version Query）、闪回事务查询（Flashback Transaction Query），关于闪回功能在这里不多赘述，官方文档链接参考（Oracle 12c）：19 Using Oracle Flashback Technology ，如下是不同闪回功能的对比矩阵：

功能依赖组件 / 参数粒度保留期典型用途
Flashback Query Undo数据 / undo_retention 行级 undo_retention 单点数据查看
Flashback Version Query Undo数据 / undo_retention 行版本级 undo_retention 变更历史追踪
Flashback Transaction Query Undo+Flashback日志 / undo_retention 事务级 undo_retention 事务分析
Flashback Table Undo数据 / undo_retention 表级 undo_retention 表数据回退
Flashback Drop 回收站 / recyclebin 对象级空间压力决定表删除恢复
Flashback Database 闪回日志 / db_flashback_retention_target 数据库级日志保留策略数据库级回滚
Flashback Data Archive 独立表空间行级用户定义(年) 合规性存档

    清楚了Oracle有哪些闪回功能，并且清楚了版本查询伪列仅在闪回版本查询（Flashback Version Query）中有效，那么之后的内容围绕Version Query Pseudocolumns（版本查询伪列）和闪回版本查询（Flashback Version Query）进行介绍。


版本查询伪列包括如下：

伪列描述
VERSIONS_STARTSCN 和 VERSIONS_STARTTIME 功能：标识行版本创建时的起始系统变更号(SCN)或时间戳(TIMESTAMP)

说明：该伪列标识数据首次具有行版本中反映的值的时间点。可用于确定Oracle闪回表或Oracle闪回查询的过去目标时间

特殊值：如果该伪列为NULL，则表示行版本在查询开始前就已存在
VERSIONS_ENDSCN 和 VERSIONS_ENDTIME 功能：标识行版本失效时的SCN或时间戳

特殊值：如果该伪列为NULL，则表示行版本在查询时仍为当前版本，或者该行对应于删除操作
VERSIONS_XID 功能：创建该行版本的事务标识符（RAW格式的数字）
VERSIONS_OPERATION 功能：事务执行的操作类型：
    ·'I'表示插入(insertion)
    ·'D'表示删除(deletion)
    ·'U'表示更新(update)

说明：行版本反映的是：
    ·插入操作后的行（INSERT）
    ·删除操作前的行（DELETE）
    ·更新操作影响的行（UPDATE）

注意：对于索引键的用户更新操作，Oracle闪回版本查询可能会将一个UPDATE操作视为两个操作（DELETE加INSERT），表现为两个版本行：先是一个’D’操作，随后是一个’I’操作（通过VERSIONS_OPERATION标识）。


闪回版本查询语法介绍：
    使用Oracle闪回版本查询（Flashback Version Query）可检索指定时间区间内特定行存在的不同版本。每次执行COMMIT语句时，都会生成一个行版本。需要注意：执行CREATE TABLE语句后，请至少等待15秒再提交任何事务，以确保Oracle闪回版本查询(Flashback Version Query)能够正确反映这些事务变更。
    使用SELECT语句的VERSIONS BETWEEN子句指定Oracle闪回版本查询(Flashback Version Query)。语法格式如下：
SELECT [伪列], 列1, 列2, ...FROM 表名 VERSIONS BETWEEN { SCN | TIMESTAMP } start AND end
[WHERE 条件];参数说明：start和end分别代表要查询时间区间的起始点和结束点表达式。该时间区间为闭区间[包含start和end时刻]。
Oracle 闪回版本查询会返回一个数据表，其中包含指定时间区间内存在过的每一个行版本所对应的记录。该结果表中的每一行都包含描述行版本元数据的伪列，通过这些信息，您可以追溯数据库中特定变更（可能是错误操作）的发生时间及操作方式。需要注意：闪回版本查询依赖 UNDO表空间中的数据，必须在UNDO_RETENTION参数保留期内查询（默认通常15分钟~24小时），但实际保留时间还取决于UNDO表空间大小

功能	依赖组件 / 参数	粒度	保留期	典型用途
Flashback Query	Undo数据 / undo_retention	行级	undo_retention	单点数据查看
Flashback Version Query	Undo数据 / undo_retention	行版本级	undo_retention	变更历史追踪
Flashback Transaction Query	Undo+Flashback日志 / undo_retention	事务级	undo_retention	事务分析
Flashback Table	Undo数据 / undo_retention	表级	undo_retention	表数据回退
Flashback Drop	回收站 / recyclebin	对象级	空间压力决定	表删除恢复
Flashback Database	闪回日志 / db_flashback_retention_target	数据库级	日志保留策略	数据库级回滚
Flashback Data Archive	独立表空间	行级	用户定义(年)	合规性存档

伪列	描述
VERSIONS_STARTSCN 和 VERSIONS_STARTTIME	功能：标识行版本创建时的起始系统变更号(SCN)或时间戳(TIMESTAMP) 说明：该伪列标识数据首次具有行版本中反映的值的时间点。可用于确定Oracle闪回表或Oracle闪回查询的过去目标时间特殊值：如果该伪列为NULL，则表示行版本在查询开始前就已存在
VERSIONS_ENDSCN 和 VERSIONS_ENDTIME	功能：标识行版本失效时的SCN或时间戳特殊值：如果该伪列为NULL，则表示行版本在查询时仍为当前版本，或者该行对应于删除操作
VERSIONS_XID	功能：创建该行版本的事务标识符（RAW格式的数字）
VERSIONS_OPERATION	功能：事务执行的操作类型： ·'I'表示插入(insertion) ·'D'表示删除(deletion) ·'U'表示更新(update) 说明：行版本反映的是： ·插入操作后的行（INSERT） ·删除操作前的行（DELETE） ·更新操作影响的行（UPDATE）

二、版本查询伪列 VS ORA_ROWSCN伪列

    ORA_ROWSCN伪列用于查看行数据被修改的最后时间，经过上面的介绍清楚了版本查询伪列主要也是追踪数据变更，只不过版本查询伪列可以查到行数据变更的所有记录(不仅是被修改的最后时间)，因此这里简单介绍一下这两个在功能和使用场景上有显著差异：


版本查询伪列：

用途： 用于闪回版本查询(Flashback Version Query)，追踪行级数据的历史变更
主要伪列：
VERSIONS_STARTTIME/VERSIONS_STARTSCN - 版本开始时间/SCN
VERSIONS_ENDTIME/VERSIONS_ENDSCN - 版本结束时间/SCN
VERSIONS_XID - 创建该版本的事务ID
VERSIONS_OPERATION - 操作类型(I/U/D)

特点：
需要显式使用 VERSIONS BETWEEN 语法
提供完整的行变更历史记录
可以查看中间版本(而不仅是最后修改)
依赖于UNDO数据，有时间限制



ORA_ROWSCN伪列：

用途： 提供行最后修改的SCN(系统变更号)
特点：
直接附加在普通查询中
每行一个SCN值(最后修改的SCN)
默认基于块级(block-level)而非行级(row-level)
需要表创建时指定 ROWDEPENDENCIES 才能实现行级精度



主要区别对比：

特性 Version Query Pseudocolumns ORA_ROWSCN
粒度行级变更历史行/块级最后修改SCN
数据源 UNDO数据数据块头信息
时间范围 需要指定时间/SCN范围总是显示当前行最后的SCN
精度精确到每次变更精确到每次变更
使用场景 审计、数据恢复、历史分析乐观锁、变更检测
DDL影响 表结构变更会导致ORA-01466 不受表结构变更影响
两者结合使用，先用ORA_ROWSCN定位可疑行，再用Version Query分析详细变更历史

特性	Version Query Pseudocolumns	ORA_ROWSCN
粒度	行级变更历史	行/块级最后修改SCN
数据源	UNDO数据	数据块头信息
时间范围	需要指定时间/SCN范围	总是显示当前行最后的SCN
精度	精确到每次变更	精确到每次变更
使用场景	审计、数据恢复、历史分析	乐观锁、变更检测
DDL影响	表结构变更会导致ORA-01466	不受表结构变更影响
两者结合使用，先用ORA_ROWSCN定位可疑行，再用Version Query分析详细变更历史

案例一：创建表，针对多次被修改的某行数据通过版本查询伪列查看更改时间，以及修改前后的数据

（1）查看liu_jybq_org_medical表结构，创建liu_jybq_org_medical_VQ新表

SQL> create table liu_jybq_org_medical_VQ
(id                  VARCHAR2(32) not null,region_id           VARCHAR2(32) not null,hospital_short_name VARCHAR2(600),hospital_full_name  VARCHAR2(600),hospital_type_code  VARCHAR2(64) not null,hospital_type_name  VARCHAR2(200),hospital_name_py    VARCHAR2(800),hospital_class_code VARCHAR2(64),hospital_class_name VARCHAR2(200),hospital_grade_code VARCHAR2(64),hospital_grade_name VARCHAR2(120),hospital_fixed_flag VARCHAR2(4),special_subject     VARCHAR2(2000),hospital_property   VARCHAR2(4),linkman_person      VARCHAR2(600),linkman_way         VARCHAR2(200),hospital_addr       VARCHAR2(2000),bank_name           VARCHAR2(200),bank_account        VARCHAR2(200),organization_code   VARCHAR2(18),hospital_location   VARCHAR2(32),hospital_profile    VARCHAR2(2000),hospital_pictures   VARCHAR2(100),remark              CLOB,del_flag            VARCHAR2(4) not null,create_user_id      VARCHAR2(32) not null,create_date         TIMESTAMP(6) not null,update_user_id      VARCHAR2(32) not null,update_date         TIMESTAMP(6) not null,region_code         VARCHAR2(10),hospital_code       VARCHAR2(50),is_high_risk        VARCHAR2(10),high_risk_reason    VARCHAR2(1000)
);   -- 不指定参数默认块级别，相关参数为NOROWDEPENDENCIESSQL> insert into liu_jybq_org_medical_VQ select * from liu_jybq_org_medical;
SQL> commit;

（2）查看特定数据的ORA_ROWSCN伪列，并转换为时间戳时

SQL>
SELECT  t1.id,t1.region_id,t1.remark,t1.hospital_name_py,t1.hospital_type_name,to_char(scn_to_timestamp(ORA_ROWSCN), 'YYYY-MM-DD HH24:MI:SS'),dbms_rowid.rowid_relative_fno(rowid) datafile_ID,dbms_rowid.rowid_block_number(rowid) block_number
FROM liu_jybq_org_medical_VQ t1
where id='00345C56B7514B3EA0F996DCFF8A0824';

在这里插入图片描述
对特定数据进行多次更新后再查看ORA_ROWSCN伪列，并转换为时间戳时

SQL> update liu_jybq_org_medical_VQ set hospital_type_name='社区医疗站' where id='00345C56B7514B3EA0F996DCFF8A0824'; 
SQL> COMMIT;SQL> update liu_jybq_org_medical_VQ set hospital_type_name='乡镇卫生所' where id='00345C56B7514B3EA0F996DCFF8A0824'; 
SQL> COMMIT;SQL>
SELECT  t1.id,t1.region_id,t1.remark,t1.hospital_name_py,t1.hospital_type_name,to_char(scn_to_timestamp(ORA_ROWSCN), 'YYYY-MM-DD HH24:MI:SS'),dbms_rowid.rowid_relative_fno(rowid) datafile_ID,dbms_rowid.rowid_block_number(rowid) block_number
FROM liu_jybq_org_medical_VQ t1
where id='00345C56B7514B3EA0F996DCFF8A0824';

可以看到了相关行数据的更新时间，但对于ORA_ROWSCN伪列而言只能看到行数据被修改的最后时间，并不能看到其他时间被修改的具体情况，那么这时候就需要使用到了版本查询伪列
在这里插入图片描述

（2）使用版本查询伪列，查看行数据多次执行的情况，以及修改前后的数据
基于时间范围的查询：

SQL>
SELECT 
versions_starttime AS change_time,        --- 版本开始时间/SCN
versions_endtime AS end_time,             --- 版本结束时间/SCNversions_xid AS transaction_id,           --- 创建该版本的事务IDversions_operation AS operation,          --- 操作类型(I/U/D)hospital_type_name AS new_hospital_type_name     --- 该行版本中的当前值
FROM liu_jybq_org_medical_VQ
VERSIONS BETWEEN TIMESTAMP 
TO_TIMESTAMP('2025-08-14 18:18:00', 'YYYY-MM-DD HH24:MI:SS') AND 
TO_TIMESTAMP('2025-08-14 18:20:00', 'YYYY-MM-DD HH24:MI:SS')
where id='00345C56B7514B3EA0F996DCFF8A0824'
ORDER BY 
versions_starttime desc nulls last;

基于SCN的查询：

SQL>
SELECT 
versions_starttime AS change_time,        --- 版本开始时间/SCN
versions_endtime AS end_time,             --- 版本结束时间/SCNversions_xid AS transaction_id,           --- 创建该版本的事务IDversions_operation AS operation,          --- 操作类型(I/U/D)hospital_type_name AS new_hospital_type_name     --- 该行版本中的当前值
FROM liu_jybq_org_medical_VQ
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where id='00345C56B7514B3EA0F996DCFF8A0824'
ORDER BY 
versions_starttime desc nulls last;

通过闪回版本查询语句配合版本查询伪列可以查出特定hospital_type_name行字段的数据更新情况，从“幸福社区站 —> 社区医疗站 —> 乡镇卫生所”，使用降序将最新数据情况放在最前面
在这里插入图片描述

案例二：在现存表上使用版本查询伪列，针对多次被修改的某行数据通过版本查询伪列查看更改时间，以及修改前后的数据

（1）查看liu_jybq_org_medical业务表特定数据的ORA_ROWSCN伪列，并转换为时间戳时
SQL>
SELECT  t1.id,t1.region_id,t1.remark,t1.hospital_name_py,t1.hospital_type_name,to_char(scn_to_timestamp(ORA_ROWSCN), 'YYYY-MM-DD HH24:MI:SS'),dbms_rowid.rowid_relative_fno(rowid) datafile_ID,dbms_rowid.rowid_block_number(rowid) block_number
FROM liu_jybq_org_medical t1
where id='004138D1DD804D0EA8F6929E15A4480D';
liu_jybq_org_medical是一个创建很久的业务表，并且有段时间没有更新数据了。如果查看特定数据的ORA_ROWSCN伪列，并转换为时间戳时，会抛出如下错误。这是因为Oracle仅会在有限时间内保留SCN（系统变更号）与其生成时间戳的映射关系，SCN（系统变更号）与其生成时间戳的映射保留时间取的是undo数据覆盖时间和闪回归档保留期的最大值，不管取值如何，scn_to_timestamp函数定义了强制最低保留时间限制，SCN 与时间戳的关联信息最少会保留 120 小时（5 天）

对特定数据进行多次更新后再查看ORA_ROWSCN伪列，并转换为时间戳时
SQL> update liu_jybq_org_medical set hospital_type_name='社区医疗站' where id='004138D1DD804D0EA8F6929E15A4480D'; 
SQL> COMMIT;SQL> update liu_jybq_org_medical set hospital_type_name='乡镇卫生所' where id='004138D1DD804D0EA8F6929E15A4480D'; 
SQL> COMMIT;SQL>
SELECT  t1.id,t1.region_id,t1.remark,t1.hospital_name_py,t1.hospital_type_name,to_char(scn_to_timestamp(ORA_ROWSCN), 'YYYY-MM-DD HH24:MI:SS'),dbms_rowid.rowid_relative_fno(rowid) datafile_ID,dbms_rowid.rowid_block_number(rowid) block_number
FROM liu_jybq_org_medical t1
where id='004138D1DD804D0EA8F6929E15A4480D';
可以看到了相关行数据的更新时间，但对于ORA_ROWSCN伪列而言只能看到行数据被修改的最后时间，并不能看到其他时间被修改的具体情况，那么这时候就需要使用到了版本查询伪列

（2）使用版本查询伪列，查看行数据多次执行的情况，以及修改前后的数据
基于时间范围的查询：
SQL>
SELECT 
versions_starttime AS change_time,        --- 版本开始时间/SCN
versions_endtime AS end_time,             --- 版本结束时间/SCNversions_xid AS transaction_id,           --- 创建该版本的事务IDversions_operation AS operation,          --- 操作类型(I/U/D)hospital_type_name AS new_hospital_type_name     --- 该行版本中的当前值
FROM liu_jybq_org_medical
VERSIONS BETWEEN TIMESTAMP 
TO_TIMESTAMP('2025-08-14 18:20:00', 'YYYY-MM-DD HH24:MI:SS') AND 
TO_TIMESTAMP('2025-08-14 18:25:00', 'YYYY-MM-DD HH24:MI:SS')
where id='004138D1DD804D0EA8F6929E15A4480D'
ORDER BY 
versions_starttime desc nulls last;
基于SCN的查询：
SQL>
SELECT 
versions_starttime AS change_time,        --- 版本开始时间/SCN
versions_endtime AS end_time,             --- 版本结束时间/SCNversions_xid AS transaction_id,           --- 创建该版本的事务IDversions_operation AS operation,          --- 操作类型(I/U/D)hospital_type_name AS new_hospital_type_name     --- 该行版本中的当前值
FROM liu_jybq_org_medical
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where id='004138D1DD804D0EA8F6929E15A4480D'
ORDER BY 
versions_starttime desc nulls last;
通过闪回版本查询语句配合版本查询伪列可以查出特定hospital_type_name行字段的数据更新情况，从“其他 —> 社区医疗站 —> 乡镇卫生所”，使用降序将最新数据情况放在最前面

总结与最佳实践
闪回版本查询为Oracle用户提供了强大的数据追溯能力，通过 VERSIONS_* 伪列可精准还原行数据变更轨迹，如同数据库的"时间机器"⏳。使用时需注意：
1️⃣ 时效性：尽早查询，避免UNDO数据因空间压力或超出UNDO_RETENTION期限被覆盖
2️⃣ 扩展性：对于长期审计需求，建议结合闪回数据归档(Flashback Data Archive)实现历史数据永久保存