database - 为什么 ora_rowscn 改变而不更新表

Question

我使用 ora_rowscn 跟踪表中更改的行并处理更新的行。

上周我注意到，对于我使用 rowdependencies 创建的一些(不是全部)表，ora_rowscn 发生了变化，表上没有任何事务。我的意思是，如果我选择 max(ora_rowscn)，我总是会得到更高的数字。

这是我如何创建表格的示例

1. 创建表

   create table test ( test_id number, txt varchar2(5) ) rowdependencies;
   

2. 向表中插入了一些数据

   insert into test values(1,'a');
   insert into test values(2,'b');
   insert into test values(3,'c');
   

3. 使用 ORA_ROWSCN 多次查询表

   select max(ora_rowscn),max(b.current_scn) from test a, v$database b
   

每次我查询时，我都会得到一个更高的max(ora_rowscn)。

在 MetaLink 上，我发现了两个报告的错误(错误 14093863、错误 9814923)，它们似乎是同一个问题，但设置为不是错误。

为什么会发生这种情况，如何解决或修复它？

Answer 1

我们询问了我们的 Oracle 联系人并得到了以下答复。

“这不是错误，它是未记录的功能。”

The ORA_ROWSCN is generated when the blocks are cleaning out the transactions. There are two kinds of block clean out may occur: fast clean out and delayed clean out.

Note that for delayed clean out, the exact commit-scn of a transaction may not be available when a block modified by that transaction is cleaned out and therefore we may get upper-bounds for the commit-scn. So, for those blocks with fast clean out we can update the ORA_ROWSCN right after the commit of a transaction;

However, for those blocks with delayed clean out we only update the corresponding ORA_ROWSCN the next time we touch the block(DML or select). It can be several hours later. It is possible that we are doing bunch of staff in other tables (thus the SCNs get incremented) and we are not doing any transaction in this table T. However when we queried the ORA_ROWSCN for table T we can still get a recent figure for SCN, since the block has just been cleaned out (but the transaction has been committed several hours ago). This ORA_ROWSCN is the upper bound of the commit time for the last transaction.

看起来问题确实存在。如果我理解正确，这种情况没有简单的解决方案。

我希望它能帮助其他用户了解问题。

在这里我发现了一些关于 block 清理的更不安全的信息

• Clean it up 乔纳森·刘易斯
• Delayed Block cleanout