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.  

• SCN System Change Number:  is a crucial data structure that defines a committed version of the database at a precise moment in time.

• When a transaction commits, it is assigned as SCN that uniquely identifies the transaction.

• SCN value never gets reset to Zero unless the database is re-created.

• SCN is a read consistent snapshots of the database that are crucial for recovery operations (oracle performs recovery based on SCN's only).

• The current system SCN can be queried using the below

                    SQL> col CURRENT_SCN format 999999999999999
                    SQL> select current_scn from v$database; 

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

• 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.

• Internally Oracle uses SCNs to track changes so any flashback operation that uses a time stamp must be translated into the nearest SCN which can result in a 3 second error. 

• Some Queries for SCN are below

                SQL> SELECT SYSTIMESTAMP FROM dual;

                SQL> SELECT CURRENT_TIMESTAMP FROM dual;

                SQL> select dbms_flashback.get_system_change_number from dual;

                SQL> SELECT CURRENT_SCN FROM V$DATABASE;

                SQL> SELECT TO_CHAR(TIME_DP,'DDMMMYYYY HH24:MI') DATE_TIME,SCN  FROM SMON_SCN_TIME;