有意思的ora_rowscn

最新推荐文章于 2025-08-21 08:30:00 发布

原创最新推荐文章于 2025-08-21 08:30:00 发布 · 486 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

案例分析

本文深入探讨了ORA_ROWSCN在数据库同步中的使用，包括其在表创建、视图查询及条件语句中的功能。同时，解释了ORA_ROWSCN不能精确反映行变化情况的原因及其在数据块级别上的特性。强调了细粒度ORA_ROWSCN在多事务操作下的行为，并提供了一个实例展示如何通过SCN获取行修改时间。

今天某用户电话问了一个有意思的问题。

核心数据库与数据仓库间用ora_rowscn做数据同步，最近发现某些表产生不必要的数据同步。

对这个概念不是很熟悉，查阅相关资料

对于每一行数据,ora_rowscn返回每一行最近被修改的大概时间.

可以通过在创建表时使用行级别的依赖跟踪来获得一个更加精确的scn.

create table ... norowdependencies|rowdependencies

在对视图进行查询时不能使用ora_rowscn.但对于视图的基表是可以使用ora_rowscn.

也能在update或delete语句中的where子句中使用ora_rowscn

获取行被修改的大根的scn

SQL> select ora_rowscn,t.test_id from test_jy t;

ORA_ROWSCN TEST_ID

---------- ---------------------

625591 3

通过scn来获取修改行记录大概的时间

这个不是重点，重点在于翻阅metalink的时候，有这样一段话

Whether at the block level or at the row level, the ORA_ROWSCN should not be considered to be an exact SCN. For example, if a transaction changed row R in a block and committed at SCN 10, it is not always true that the ORA_ROWSCN for the row would return 10. While a value less than 10 would never be returned, any value greater than or equal to 10 could be returned. That is, the ORA_ROWSCN of a row is not always guaranteed to be the exact commit SCN of the transaction that last modified that row. However, with fine-grained ORA_ROWSCN, if two transactions T1 and T2 modified the same row R, one after another, and committed, a query on the ORA_ROWSCN of row R after the commit of T1 will return a value lower than the value returned after the commit of T2. If a block is queried twice, then it is possible for the value of ORA_ROWSCN to change between the queries even though rows have not been updated in the time between the queries. The only guarantee is that the value of ORA_ROWSCN in both queries is greater than the commit SCN of the transaction that last modified that row.

ora_rowscn不能精确反应行的变化情况，因为它是数据块级别的。

特此记录。