《博主主页》 CSDN主页__奈斯DB IF Club社区主页__奈斯、《擅长领域》擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控并对SQLserver、NoSQL(Redis)有了解如果觉得文章对你有所帮助欢迎点赞收藏加关注话接上文在上文中了解了通过ORA_ROWSCN伪列可以精准定位行数据的最后修改时间⏰。但是如果有这样一个场景不仅想知道行数据被修改的时间还想知道修改前的旧值以及这行数据被修改了多少次需要进行数据追溯那这个有没有办法实现的呢Oracle表示当然可以✨ 通过闪回版本查询(Flashback Version Query)的伪列就可以轻松实现VERSIONS_STARTTIME/VERSIONS_STARTSCN- 版本开始时间/SCNVERSIONS_ENDTIME/VERSIONS_ENDSCN- 版本结束时间/SCNVERSIONS_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实现Top-N查询和传统分页(含通过 ROW_NUMBER()函数 的实现方式)第六篇伪列之Hierarchical Query、COLUMN_VALUE、OBJECT_ID、OBJECT_VALUE、XMLDATA特别说明本篇文章部分理论性知识点均来源于版权归 Oracle 所有的官方公开文档手册并结合了我个人的解读和案例演示。若需要调整请联系会尽快处理官方文档对于版本查询伪列的介绍Oracle 12cVersion 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 12c19 Using Oracle Flashback Technology 如下是不同闪回功能的对比矩阵功能依赖组件 / 参数粒度保留期典型用途Flashback QueryUndo数据 / undo_retention行级undo_retention单点数据查看Flashback Version QueryUndo数据 / undo_retention行版本级undo_retention变更历史追踪Flashback Transaction QueryUndoFlashback日志 / undo_retention事务级undo_retention事务分析Flashback TableUndo数据 / 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表名 VERSIONSBETWEEN{ SCN|TIMESTAMP}startANDend[WHERE条件];参数说明start和end分别代表要查询时间区间的起始点和结束点表达式。该时间区间为闭区间[包含start和end时刻]。Oracle 闪回版本查询会返回一个数据表其中包含指定时间区间内存在过的每一个行版本所对应的记录。该结果表中的每一行都包含描述行版本元数据的伪列通过这些信息您可以追溯数据库中特定变更可能是错误操作的发生时间及操作方式。需要注意闪回版本查询依赖 UNDO表空间 中的数据必须在UNDO_RETENTION参数保留期内查询默认通常15分钟~24小时但实际保留时间还取决于UNDO表空间大小二、版本查询伪列 VS ORA_ROWSCN伪列ORA_ROWSCN伪列用于查看行数据被修改的最后时间经过上面的介绍清楚了版本查询伪列主要也是追踪数据变更只不过版本查询伪列可以查到行数据变更的所有记录(不仅是被修改的最后时间)因此这里简单介绍一下这两个在功能和使用场景上有显著差异版本查询伪列用途用于闪回版本查询(Flashback Version Query)追踪行级数据的历史变更主要伪列VERSIONS_STARTTIME/VERSIONS_STARTSCN- 版本开始时间/SCNVERSIONS_ENDTIME/VERSIONS_ENDSCN- 版本结束时间/SCNVERSIONS_XID- 创建该版本的事务IDVERSIONS_OPERATION- 操作类型(I/U/D)特点需要显式使用VERSIONS BETWEEN语法提供完整的行变更历史记录可以查看中间版本(而不仅是最后修改)依赖于UNDO数据有时间限制ORA_ROWSCN伪列用途提供行最后修改的SCN(系统变更号)特点直接附加在普通查询中每行一个SCN值(最后修改的SCN)默认基于块级(block-level)而非行级(row-level)需要表创建时指定ROWDEPENDENCIES才能实现行级精度主要区别对比特性Version Query PseudocolumnsORA_ROWSCN粒度行级变更历史行/块级最后修改SCN数据源UNDO数据数据块头信息时间范围需要指定时间/SCN范围总是显示当前行最后的SCN精度精确到每次变更精确到每次变更使用场景审计、数据恢复、历史分析乐观锁、变更检测DDL影响表结构变更会导致ORA-01466不受表结构变更影响两者结合使用先用ORA_ROWSCN定位可疑行再用Version Query分析详细变更历史案例一创建表针对多次被修改的某行数据通过版本查询伪列查看更改时间以及修改前后的数据1查看liu_jybq_org_medical表结构创建liu_jybq_org_medical_VQ新表SQLcreatetableliu_jybq_org_medical_VQ(id VARCHAR2(32)notnull,region_id VARCHAR2(32)notnull,hospital_short_name VARCHAR2(600),hospital_full_name VARCHAR2(600),hospital_type_code VARCHAR2(64)notnull,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)notnull,create_user_id VARCHAR2(32)notnull,create_dateTIMESTAMP(6)notnull,update_user_id VARCHAR2(32)notnull,update_dateTIMESTAMP(6)notnull,region_code VARCHAR2(10),hospital_code VARCHAR2(50),is_high_risk VARCHAR2(10),high_risk_reason VARCHAR2(1000));-- 不指定参数默认块级别相关参数为NOROWDEPENDENCIESSQLinsertintoliu_jybq_org_medical_VQselect*fromliu_jybq_org_medical;SQLcommit;2查看特定数据的ORA_ROWSCN伪列并转换为时间戳时SQLSELECTt1.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_numberFROMliu_jybq_org_medical_VQ t1whereid00345C56B7514B3EA0F996DCFF8A0824;对特定数据进行多次更新后再查看ORA_ROWSCN伪列并转换为时间戳时SQLupdateliu_jybq_org_medical_VQsethospital_type_name社区医疗站whereid00345C56B7514B3EA0F996DCFF8A0824;SQLCOMMIT;SQLupdateliu_jybq_org_medical_VQsethospital_type_name乡镇卫生所whereid00345C56B7514B3EA0F996DCFF8A0824;SQLCOMMIT;SQLSELECTt1.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_numberFROMliu_jybq_org_medical_VQ t1whereid00345C56B7514B3EA0F996DCFF8A0824;可以看到了相关行数据的更新时间但对于ORA_ROWSCN伪列而言只能看到行数据被修改的最后时间并不能看到其他时间被修改的具体情况那么这时候就需要使用到了版本查询伪列3使用版本查询伪列查看行数据多次执行的情况以及修改前后的数据基于时间范围的查询SQLSELECTversions_starttimeASchange_time,--- 版本开始时间/SCNversions_endtimeASend_time,--- 版本结束时间/SCNversions_xidAStransaction_id,--- 创建该版本的事务IDversions_operationASoperation,--- 操作类型(I/U/D)hospital_type_nameASnew_hospital_type_name--- 该行版本中的当前值FROMliu_jybq_org_medical_VQ VERSIONSBETWEENTIMESTAMPTO_TIMESTAMP(2025-08-14 18:18:00,YYYY-MM-DD HH24:MI:SS)ANDTO_TIMESTAMP(2025-08-14 18:20:00,YYYY-MM-DD HH24:MI:SS)whereid00345C56B7514B3EA0F996DCFF8A0824ORDERBYversions_starttimedescnullslast;基于SCN的查询SQLSELECTversions_starttimeASchange_time,--- 版本开始时间/SCNversions_endtimeASend_time,--- 版本结束时间/SCNversions_xidAStransaction_id,--- 创建该版本的事务IDversions_operationASoperation,--- 操作类型(I/U/D)hospital_type_nameASnew_hospital_type_name--- 该行版本中的当前值FROMliu_jybq_org_medical_VQ VERSIONSBETWEENSCN MINVALUEANDMAXVALUEwhereid00345C56B7514B3EA0F996DCFF8A0824ORDERBYversions_starttimedescnullslast;通过闪回版本查询语句配合版本查询伪列可以查出特定hospital_type_name行字段的数据更新情况从“幸福社区站 — 社区医疗站 — 乡镇卫生所”使用降序将最新数据情况放在最前面案例二在现存表上使用版本查询伪列针对多次被修改的某行数据通过版本查询伪列查看更改时间以及修改前后的数据1查看liu_jybq_org_medical业务表特定数据的ORA_ROWSCN伪列并转换为时间戳时SQLSELECTt1.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_numberFROMliu_jybq_org_medical t1whereid004138D1DD804D0EA8F6929E15A4480D;liu_jybq_org_medical是一个创建很久的业务表并且有段时间没有更新数据了。如果查看特定数据的ORA_ROWSCN伪列并转换为时间戳时会抛出如下错误。这是因为Oracle仅会在有限时间内保留SCN系统变更号与其生成时间戳的映射关系SCN系统变更号与其生成时间戳的映射保留时间取的是undo数据覆盖时间和闪回归档保留期的最大值不管取值如何scn_to_timestamp函数定义了强制最低保留时间限制SCN 与时间戳的关联信息最少会保留 120 小时5 天对特定数据进行多次更新后再查看ORA_ROWSCN伪列并转换为时间戳时SQLupdateliu_jybq_org_medicalsethospital_type_name社区医疗站whereid004138D1DD804D0EA8F6929E15A4480D;SQLCOMMIT;SQLupdateliu_jybq_org_medicalsethospital_type_name乡镇卫生所whereid004138D1DD804D0EA8F6929E15A4480D;SQLCOMMIT;SQLSELECTt1.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_numberFROMliu_jybq_org_medical t1whereid004138D1DD804D0EA8F6929E15A4480D;可以看到了相关行数据的更新时间但对于ORA_ROWSCN伪列而言只能看到行数据被修改的最后时间并不能看到其他时间被修改的具体情况那么这时候就需要使用到了版本查询伪列2使用版本查询伪列查看行数据多次执行的情况以及修改前后的数据基于时间范围的查询SQLSELECTversions_starttimeASchange_time,--- 版本开始时间/SCNversions_endtimeASend_time,--- 版本结束时间/SCNversions_xidAStransaction_id,--- 创建该版本的事务IDversions_operationASoperation,--- 操作类型(I/U/D)hospital_type_nameASnew_hospital_type_name--- 该行版本中的当前值FROMliu_jybq_org_medical VERSIONSBETWEENTIMESTAMPTO_TIMESTAMP(2025-08-14 18:20:00,YYYY-MM-DD HH24:MI:SS)ANDTO_TIMESTAMP(2025-08-14 18:25:00,YYYY-MM-DD HH24:MI:SS)whereid004138D1DD804D0EA8F6929E15A4480DORDERBYversions_starttimedescnullslast;基于SCN的查询SQLSELECTversions_starttimeASchange_time,--- 版本开始时间/SCNversions_endtimeASend_time,--- 版本结束时间/SCNversions_xidAStransaction_id,--- 创建该版本的事务IDversions_operationASoperation,--- 操作类型(I/U/D)hospital_type_nameASnew_hospital_type_name--- 该行版本中的当前值FROMliu_jybq_org_medical VERSIONSBETWEENSCN MINVALUEANDMAXVALUEwhereid004138D1DD804D0EA8F6929E15A4480DORDERBYversions_starttimedescnullslast;通过闪回版本查询语句配合版本查询伪列可以查出特定hospital_type_name行字段的数据更新情况从“其他 — 社区医疗站 — 乡镇卫生所”使用降序将最新数据情况放在最前面总结与最佳实践闪回版本查询为Oracle用户提供了强大的数据追溯能力通过VERSIONS_* 伪列可精准还原行数据变更轨迹如同数据库的时间机器⏳。使用时需注意1️⃣ 时效性尽早查询避免UNDO数据因空间压力或超出UNDO_RETENTION期限被覆盖2️⃣ 扩展性对于长期审计需求建议结合闪回数据归档(Flashback Data Archive)实现历史数据永久保存