Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

database - Why does ora_rowscn change without updating a table

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

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. creating table

    create table test ( test_id number, txt varchar2(5) ) rowdependencies;
    
  2. Inserted some data into the table

    insert into test values(1,'a');
    insert into test values(2,'b');
    insert into test values(3,'c');
    
  3. Queried the table more than once with ORA_ROWSCN

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

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

we asked our Oracle contact and got the following answer.

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

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.

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...