为什么ora_rowscn改变而不更新表

2021-12-30 00:00:00 database oracle11g oracle

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

I work with ora_rowscn to track the changed rows on a table and to work with updated rows.

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

Last week I noticed that for some (not all) of my tables that I created with rowdependencies, the ora_rowscn changes without any transactions on the table. I mean if I select max(ora_rowscn) I get always higher number.

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

Here is an example how I created my table

  1. 创建表格

  1. creating table 

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

  • 在表格中插入了一些数据

  • Inserted some data into the table 

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

  • 多次使用 ORA_ROWSCN 查询表

  • Queried the table more than once with ORA_ROWSCN

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

    • 每次查询都会得到更高的max(ora_rowscn).

    Every time I queried I got a higher max(ora_rowscn).

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

    On MetaLink i found two reported Bugs (Bug 14093863, Bug 9814923) that seems to be the same problem, but set as not a Bug.

    为什么会发生这种情况，有什么解决方案可以让它工作或修复它?

    Why does it happen and what is the solution to get it work or fix it?

    推荐答案

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

    we asked our Oracle contact and got the following answer.

    这不是错误，而是未记录的功能."

    "It is not a bug, its undocumented feature."

    ORA_ROWSCN 在块清理时生成交易.有两种可能会发生块清除:快速清理和延迟清理.

    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.

    请注意，对于延迟清除，事务的确切 commit-scn当由该交易修改的块时，可能不可用清除了，因此我们可以获得 commit-scn 的上限.因此，对于那些快速清除的块，我们可以更新 ORA_ROWSCN在事务提交之后；

    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;

    然而，对于那些延迟清除的块，我们只更新下次我们触摸块时对应的 ORA_ROWSCN(DML 或选择).它可以是几个小时后.有可能我们是在其他表中做一堆工作人员(因此 SCN 增加)并且我们没有在这个表 T 中做任何交易.但是当我们查询表 T 的 ORA_ROWSCN 我们仍然可以得到一个最近的数字对于 SCN，因为块刚刚被清除(但是交易已在几个小时前提交).这个 ORA_ROWSCN 是最后一个事务的提交时间上限.

    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.

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

    How it looks the problem does exist. If I have understood correctly, there no simple solution for this case.

    我希望它有助于了解其他用户的问题.

    I hope it would help to understand other user the problem.

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

    Here i found some more unsefull information about block cleanout

    • 清理 作者:乔纳森·刘易斯
    • 延迟块清理
    • Clean it up by Jonathan Lewis
    • Delayed Block cleanout

    相关文章