ORA_ROWSCN - old/restored blocks don't have a real SCN assigned?

Question

We're using the ORA_ROWSCN pseudocolumn in order to locate recently modified rows for the purposes of replicating selected details over to a different data source. We're aware of the "approximate" nature of this column, that whole batches of rows within a block are marked with the SCN if only one row in the block has changed, and we are fine with that, relatively small batches of false positives are not a problem.

What we are observing however is a large number of rows that appears to have a "floating" ORA_ROWSCN value. These are millions of rows that are certainly not changing at all, however each time we begin a new console session with Oracle, the block of rows gets a brand new, most recent SCN each time. Below illustrates three separate console sessions within the span of a few minutes:

Session #1 - 4 million rows under SCN 27501512:

SQL> SELECT count(*), ORA_ROWSCN FROM our_table GROUP BY ORA_ROWSCN ORDER BY ORA_ROWSCN;

  COUNT(*) ORA_ROWSCN
---------- ----------
    12   27323587
    12   27415360
    20   27431509
   4057846   27501512

Session #2 - 4 million rows under SCN 27501522:

SQL> SELECT count(*), ORA_ROWSCN FROM our_table GROUP BY ORA_ROWSCN ORDER BY ORA_ROWSCN;

  COUNT(*) ORA_ROWSCN
---------- ----------
    12   27323587
    12   27415360
    20   27431509
   4057846   27501522

Session #3 - 4 million rows under SCN 27501528:

SQL> SELECT count(*), ORA_ROWSCN FROM our_table GROUP BY ORA_ROWSCN ORDER BY ORA_ROWSCN;

  COUNT(*) ORA_ROWSCN
---------- ----------
    12   27323587
    12   27415360
    20   27431509
   4057846   27501528

This is a test database and no other process is modifying rows. Our theory is that the rows in this block of four million for some reason don't have a dedicated "SCN", as these rows were transferred into this database using the Oracle Data Pump tool perhaps the blocks containing them don't have a properly assigned SCN. Oracle then has no choice but to give us the highest possible SCN for these rows, corresponding to the current SCN value perhaps, since there is no other value available. When we UPDATE these rows, even meaninglessly, they move out of the block of 4 million "floating" SCNs and gain a fixed SCN number. The remaining rows keep moving around.

Can someone confirm that A. this is in fact what we're seeing, B. perhaps if this is a known effect of the Oracle Pump utility and C. that if we just mark these rows with a new UPDATE, they'll permanently move out of the "floating" SCN thus solving our issue?

Notes:

1. We know SCNs aren't accurate and are per-block.

2. We have no interest in "why don't you use alternative technique X?" answers, we know the other techniques and we will use them if we decide, we are just trying to understand this exact behavior.

Answer 1

A) What you're seeing is a real problem others have encountered. B) This problem is not only caused by Data Pump. C) UPDATE will help the problem but you cannot rely on it always working.

ORA_ROWSCN is both inaccurate and inconsistent. The 10g documention only mentions the inaccuracy. The 11g documentation makes it clear just how ill-suited ORA_ROWSCN is for what you're attempting to do:

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.

I'm not sure what causes ORA_ROWSCN to change between sessions, but I don't think it is only related to Data Pump. When I first encountered this problem about 5 years ago we never found a pattern, and if I remember correctly we didn't even use Data Pump.

Our problem specifically was with Oracle SQL Developer, which uses ORA_ROWSCN for optimistic locking. This bug was incredibly annoying. Users would make changes and when they went to commit it they were incorrectly told someone else had already changed the row. Like you are doing now, we found that if we applied any type of change to the rows the problem would go away. I can't remember how well that worked, but you should not assume it will work 100% of the time.

As far as I know nobody can explain exactly how ORA_SCN is set and what it will return. It should not be used when you need accurate or repeatable results.