SCN, the System Change Number

The scn is an ever-increasing number. It can be used to determine the "age" of the database and its component datafiles.

The current system SCN can be queried using dbms_flashback.get_system_change_number.

The SCN of the last checkpoint can be found in v$database.checkpoint_change#.

The SCN is needed in a flashback table .. to scn ... statement.

The SCN plays an important role to determine if the database is in a consistent state when the database is brought online. SMON checks the SCN in all datafile headers when the database is started. Everything is OK if all of these SCNs matches the SCN found in the controlfile. If the SCNs don't match, the database is in an inconsistent state.

smon_scn_time allows to roughly find out which SCN was current for a specific time in the last five days.

The SCN is incremented whenever a transaction commits. However, this is not the only source of increments. In a seemingly idle database, the SCN gets incremented also through AQ, SMON, job queues...

The SCN plays a vital role for providing consistent reads.

Basically, it works as follows: The query reads a db block. This block has as an attribute the SCN when it was last changed. If this SCN is greater than the SCN that was in place when (our) query began, it means that the block was changed after we have started our query. So we have to find an older version of the block. If this block is found in the rollback segments, we use it for our query.

The v$ views use change# to report SCN values. Hence, it can be argued that SCN means System Change Number, not System Commit Number.

When exporting, an SCN can be specified with the exp_flashback_scn export parameter.

With Oracle 10g, it is possible to go back to a specific SCN on a table with flashback table to scn.

See also the ora_rowscn pseudo column.

scn_to_timestamp