💫《博主主页》：
🔎 CSDN主页__奈斯DB
🔎 IF Club社区主页__奈斯、
🔥《擅长领域》：擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控；并对SQLserver、NoSQL(Redis)有了解
💖如果觉得文章对你有所帮助，欢迎点赞收藏加关注💖
首先抛出一个问题🎯： 当数据库中的数据被修改时，有没有办法找出是什么时候被修改的呢🔍？大多数小伙伴应该是可以想到通过审计日志📜或者对归档日志进行挖掘🔄。对于这两种方式，虽然都能找出和被修改数据匹配的SQL语句，以及SQL语句执行时间和执行用户，但在实际业务场景中，往往面临更复杂的情况😱：
- 单行修改✅： 可以通过审计日志直接关联执行时间
- 批量操作🌀： 一个UPDATE可能影响成千上万行（例如定期任务更新状态），这时只知道SQL执行时间，却无法确定某一行具体何时被修改
- 间接变更🕵️： 触发器、存储过程或级联更新导致的数据变动，更难直接关联到原始操作
那么，有没有办法精准追踪行数据的变更时间呢🔍？这时就需要更精细的追踪手段——Oracle的ORA_ROWSCN伪列。ORA_ROWSCN伪列精准定位数据行的最后修改时间，这个伪列记录了每行数据的系统变更号（SCN），结合 SCN_TO_TIMESTAMP 函数📊，可以将其转换成具体的时间戳⏱️，即使在批量操作以及间接变更中也能定位到具体的行级变更时间戳，从而实现行级数据变更的精准追踪。
在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 所有的官方公开文档手册，并结合了我个人的解读和案例演示。若需要调整，请联系，会尽快处理😄
官方文档对于ORA_ROWSCN伪列的介绍（Oracle 12c）：
ORA_ROWSCN Pseudocolumn
目录
一、ORA_ROWSCN介绍：
ORA_ROWSCN反映行数据最后更改的系统更改编号（SCN）。这种变化可以在块级别（粗略），也可以在行级别（细粒度）。默认情况下ORA_ROWSCN显示的是块级别（粗略）。
无论是在块级别（粗略）还是在行级别（细粒度），ORA_ROWSCN都不应被视为精确的SCN。例如，如果事务更改了块中的行R并在SCN 10处提交，则该行的ORA_ROWSCN将返回10并不总是正确的。虽然永远不会返回小于10的值，但可以返回任何大于或等于10的值。也就是说，一行的ORA_ROWSCN并不总是保证是上次修改该行的事务的确切提交SCN。然而，对于行级别（细粒度）的ORA_ROWSCN，如果两个事务T1和T2相继修改并提交了同一行R，则在提交T1之后对行R的ORA_OWSCN的查询将返回低于在提交T2之后返回的值的值。如果一个块被查询了两次，那么ORA_ROWSCN的值可能会在查询之间发生变化，即使在查询之间的时间内没有更新行。唯一的保证是两个查询中ORA_ROWSCN的值都大于上次修改该行的事务的提交SCN。
不能在对视图的查询中使用ORA_ROWSCN伪列。但是，可以在创建视图时使用它来引用基础表。也可以在UPDATE或DELETE语句的WHERE子句中使用此伪列。
二、ORA_ROWSCN限制：
- 闪回查询不支持ORA_ROWSCN。但Oracle专门为闪回查询功能，设计了的版本查询伪列（Version Query Pseudocolumns），关于版本查询伪列参考：
- 外部表不支持此伪列。
三、在创建表时定义ORA_ROWSCN伪列是块还是行级别：定义ORA_ROWSCN伪列是块还是行级别只能通过创建表实现，默认现有表是块级别，如果需要修改为行级别只能删除表然后指定行级别的参数，无论是在块级别（粗略）还是在行级别（细粒度），ORA_ROWSCN都不应被视为精确的SCN，所以块级别就够了
在创建表create table时通过
NOROWDEPENDENCIES | ROWDEPENDENCIES子句用于指定表是否使用行级依赖项跟踪。使用此功能，表中的每一行都有一个系统更改编号（SCN），该编号表示的时间大于或等于修改该行的最后一个事务的提交时间。创建表后不能更改此设置。
1）行级别ROWDEPENDENCIES：
如果要启用行级依赖项跟踪，请指定ROWDEPENDENCIES。此设置主要用于允许在复制环境中进行并行传播。它将每行的大小增加6个字节。
设置为ROWDEPENDENCIES参数后的限制：Oracle不支持对使用行级依赖项跟踪的表进行表压缩。如果同时指定ROWDEPENDENCIES子句和table_compression子句，则会忽略table_compression子句。要删除ROWDEPENDENCIES属性，必须使用DBMS_REDEFINITION包重新定义该表或重新创建该表。
2）块级别NOROWDEPENDENCIES：
如果不希望表使用行级依赖项跟踪功能，请指定NOROWDEPENDENCIES。这是默认设置。
四、SCN_TO_TIMESTAMP函数
这里需要介绍一下SCN_TO_TIMESTAMP函数，因为ORA_ROWSCN反映行数据最后更改的系统更改编号（SCN），并不是具体的某个时间，因此需要通过SCN_TO_TIMESTAMP函数将SCN转换成可读性更高的时间戳，那么转换后的时间就是行数据被修改的最后时间。
介绍：
SCN_TO_TIMESTAMP函数接受一个可计算为系统更改号（SCN）的数字作为参数，并返回与该SCN相关联的近似时间戳，返回值的数据类型为TIMESTAMP。想了解与某个SCN相关联的时间戳时，这个函数都很有用。例如，它可以与ORA_ROWSCN伪列结合使用，以将时间戳与行的最近一次更改相关联。
结果值的通常精度误差为3秒。
需要注意Oracle会在一段有限的时间内保留SCN生成时其与时间戳的关联信息。SCN_TO_TIMESTAMP 函数所依赖的 SCN 与时间戳的关联信息保留时间，取决于以下因素：
- 与数据库的自动撤销管理（Automatic Undo Management）模式相关
- 若数据库运行在自动撤销管理模式下，关联信息的保留时间会参考自动调整的撤销保留期，
也就是说SCN转换为时间戳的保留时间，受限于UNDO_RETENTON参数的设置。- 与闪回归档（flashback archives）的保留时间相关
- 数据库中所有闪回归档的保留时间也是重要参考，关联信息保留时间需考虑这些闪回归档的保留时间。
闪回归档其实可以理解为长时间的保存undo数据，对某些重要的表可以自定义它的历史记录保存期限，与undo 表空间十分相似，首先创建闪回归档表空间，并指定超长时间的undo保留时间，然后创建业务表时最后加上flashback archive参数，就可以让相关业务表的 undo 信息超长时间保留。- 最低保留时间限制
- 保留时间取上述中的最大值，不过上述两个因素计算出的结果如何，SCN 与时间戳的关联信息最少会保留 120 小时（5 天），
120 小时是SCN转换为时间戳的最低保留时间限制。
此外，需要注意：只有当数据库处于打开状态时，关联信息的保留时间才会正常 “流逝”（即倒计时生效）；若数据库关闭，保留时间不会减少。如果查询的 SCN 超出了上述保留时间范围，
函数会返回错误。
语法：SCN_TO_TIMESTAMP(number)
案例一：创建表，设置行级别显示ORA_ROWSCN，并对行进行测试（默认块级别显示ORA_ROWSCN）
（1）查看liu_jybq_org_medical表结构，创建liu_jybq_org_medical_row新表并设置行级别显示ORA_ROWSCN
SQL> create table liu_jybq_org_medical_row ( 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) ) ROWDEPENDENCIES ; SQL> insert into liu_jybq_org_medical_row select * from liu_jybq_org_medical; SQL> commit;
（2）查看数据存储在那个数据文件的那个块中，并查看现有数据的系统更改编号（SCN）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_row t1;同个块的SCN（时间）相同，不同块之间的SCN（时间）也相同：因为可能存在数据进行批处理，所以那一批的数据的SCN（时间）是一样的。
（3）修改267块中的其中一行，观察267块的SCN（时间）SQL> update liu_jybq_org_medical_row set hospital_type_name='社区医疗站' where id='001635E4ADA54097A91C40D975E7C45A'; 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_row t1;267号块总共有20行数据，只有修改行的SCN（时间）发生了变化，变成了最新更新的时间2025-08-04 22:45:43。其他同个块的数据，和不同块的时间也就是267号块的时间不受影响，没有发生变化
案例二：创建表，设置块级别显示ORA_ROWSCN，并对行进行测试（默认块级别显示ORA_ROWSCN）
（1）查看liu_jybq_org_medical表结构，创建liu_jybq_org_medical_row新表并设置行级别显示ORA_ROWSCN
SQL> create table liu_jybq_org_medical_block ( 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) ); -- 不指定参数默认块级别，相关参数为NOROWDEPENDENCIES SQL> insert into liu_jybq_org_medical_block select * from liu_jybq_org_medical; SQL> commit;
（2）查看数据存储在那个数据文件的那个块中，并查看现有数据的系统更改编号（SCN）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_block t1;同个块的SCN（时间）相同，不同块之间的SCN（时间）也相同：因为可能存在数据进行批处理，所以那一批的数据的SCN（时间）是一样的。
（3）修改5083块中的其中一行，观察5083块的SCN（时间）SQL> update liu_jybq_org_medical_block set hospital_type_name='社区医疗站' where id='001635E4ADA54097A91C40D975E7C45A'; 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_block t1;5083号块总共有20行数据，SCN（时间）都发生了变化，变成了最新更新的时间2025-08-04 23:03:02。其他块的时间，也就是5084号块的时间不受影响
案例三：ORA_ROWSCN伪列对于现存表的限制
（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;liu_jybq_org_medical是一个创建很久的业务表，并且有段时间没有更新数据了。如果查看业务全表的ORA_ROWSCN伪列，并转换为时间戳时，会抛出如下错误。这是因为Oracle仅会在有限时间内保留SCN（系统变更号）与其生成时间戳的映射关系，SCN（系统变更号）与其生成时间戳的映射保留时间取的是undo数据覆盖时间和闪回归档保留期的最大值，不管取值如何，scn_to_timestamp函数定义了强制最低保留时间限制，SCN 与时间戳的关联信息最少会保留 120 小时（5 天）
（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 t1 where id='001635E4ADA54097A91C40D975E7C45A';同样的原因，SCN（系统变更号）与其生成时间戳超出了Oracle的保留时间
对特定数据更新后再查看ORA_ROWSCN伪列，并转换为时间戳时SQL> update liu_jybq_org_medical set hospital_type_name='社区医疗站' where id='001635E4ADA54097A91C40D975E7C45A'; 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='001635E4ADA54097A91C40D975E7C45A';可以看到了相关行数据的更新时间
总结：由此可以得出结论，查看行数据的ORA_ROWSCN伪列，并通过scn_to_timestamp函数转换为时间戳时，SCN（系统变更号）与其生成时间戳的映射保留时间取的是undo数据覆盖时间和闪回归档保留期的最大值，不管取值如何，scn_to_timestamp函数定义了强制最低保留时间限制，SCN 与时间戳的关联信息最少会保留 120 小时（5 天）
从审计日志的模糊定位🔍，到ORA_ROWSCN伪列的行级时间追踪⏱️，Oracle为我们提供了从宏观到微观的数据变更追溯能力。无论是批量操作的’范围伤害’💥，还是触发器的’暗箱操作’🕵️♂️，这个SCN时间戳神器都能精准锁定每一行数据的’案发时刻’！
扫一扫
918
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
