A system change number (SCN) is a logical, internal time stamp used by Oracle Database.
SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo.
The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.
SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time, and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time in the database. Several events may share the same SCN, which means that they occurred at the same time in the database.
Every transaction has an SCN.
For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.
Oracle Database increments SCNs in the system global area (SGA). When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. The log writer process then writes the commit record of the transaction immediately to the online redo log. The commit record has the unique SCN of the transaction.
Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.
SCN Format and Structure
SCN is a huge number with two components to it: SCN Base & SCB Wrap.
SCN is a 6 byte (48 bits) number. Out of these 48 bits, SCN_WRAP is a 16 bit (2 Bytes) number and SCN_BASE is a 32 bit (4 Bytes) number. Both BASE & WRAP are used to control the SCN’s increment and to ensure that the database won’t run out of it. SCN_WRAP is incremented by 1 when SCN_BASE reaches the value of 4 Billion and SCN_BASE becomes 0.
From Oracle Version 12c, the SCN number is an 8 byte number.
So how do we see the current SCN value? The easiest way is to query the view V$DATABASE. Have a look:
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
3628034
As we can see, that SCN is displayed as a number. This is good because it makes the use of SCN easy for us in our statements performing recovery, flashback, etc. If we want, we can convert SCN to a Hexadecimal value as well:
SQL> select to_char('3628034','xxxxxxxx') scn_hex from dual;
SCN_HEX
---------
362ce
