I was doing some research to try and understand a little more about the ORA_ROWSCN capability. I am hoping someone can explain what I am seeing.
For my testing I created the following table.
CREATE TABLE TEST3
(
COL1 NUMBER,
USERS VARCHAR2(10) DEFAULT USER,
USERS_SID NUMBER DEFAULT SYS_CONTEXT('USERENV','SID'),
TIME_STAMP DATE DEFAULT SYSDATE
);
I inserted rows into this table - I first connected as SYSTEM, reconnected as SYSTEM, and then connected as CODY. The first session created for the SYSTEM login was SID 255 (as shown in the output). The next session created for the SYSTEM login was SID 367. The next session created for the CODY login was SID 255. This was the same SID used earlier by the SYSTEM login. Below is the table's data including the SCN. What I found interesting was that when a change was made from a given SID, all rows that were previously inserted or updated for that SID got the new SCN - not just the new rows. You can see from the output that the time_stamp field includes the time the record was actually changed.
My question is: why does each row previously updated by a given session get a new SCN for each change made from that session? For example, in the output you can see that the last record was inserted at 11:52 and the first record inserted for that session (255) was at 11:32. However for all records with SID 255 the SCN was updated with the last change.
