Friday, August 3, 2012

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;

No comments: