Is my understanding regrding SCN true ?

[Lavjeet]

1. This is regarding the behaviour of SCN and Rollback(Undo) Segment:
In this case two users are logged in Database.When is the new SCN number generated ? When the user fires a DML statement or when he commits ?

2. Let say user1 fires a query on Emp table and the rows are not in Database buffer cache.The process will get a SCN number first which will be the current latest in the database . Let say it is 111. The process gets the data from the Data file and copies it on the cache.So what is the state of the data block and undo blocks ?
Will the undo block will have the same content as data block or will be empty ?

Data block
Data SCN
10 111
20 111
30 111
40 111

Undo block
Data SCN





3. User1 is reading the data from the data blocks and in between user2 updates a row (having value = 30 ) but has not committed it. Will now a new SCN number be generated ? Now the old data will be copied to undo segment . Now lets say user2 updates it again and does not commit so will the state be as shown below ?

Data block
Data SCN
10 111
20 111
35 112
40 111

Undo segment
Data SCN
30 111



State after 1st update


Data block
Data SCN
10 111
20 111
37 113
40 111

Undo segment
Data SCN
30 111
35 112


State after 2st update.

Is this understanding correct ? If yes then if now user2 rollbacks then will the SCN revert back ?

[tamilselvan]

The SCN is generated when commit occurs.

In between 2 commits, oracle generates many SCNs for its internal use.