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:
We know SCNs aren't accurate and are per-block.
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.