The Oracle System Change Number (SCN) is a logical, internal timestamp that Oracle uses to track every committed change to the database. Each time a transaction commits, Oracle assigns it a unique SCN – an ever-increasing number that establishes the exact ordering of all changes. Oracle uses the SCN to enforce read consistency (ensuring queries see a stable snapshot of data), to manage crash recovery (by comparing SCN values across control files, redo logs, and data file headers), and to coordinate operations like RMAN backups, Data Guard replication, and distributed transactions. Understanding SCN is essential for DBAs working with recovery scenarios, flashback queries, and database cloning.<\/p>\n
For many people working with Oracle database, SCN (System Change Number) is a topic that interests them greatly \u2013 but despite that it\u2019s a fascinating topic, it\u2019s confusing as well. In this article we will learn the nitty-gritty of SCN: what it is, where it is used, and how it works.<\/p>\n
It\u2019s worth mentioning that this is not and cannot be complete coverage of every detail about SCN. With that said, let\u2019s start.<\/p>\n
Assume it\u2019s the end of the month and its payday. You are in the Payroll group and, with appropriate authority, have issued a query to pull the currently salary calculation of all the employees. Numbers are flowing across the screen, everything is going great.<\/p>\n
Suppose, while you are looking at the output, a colleague starts a new pay run and that batch job clears the current calculation summary. Things would certainly become confusing if your output suddenly showed \u20180\u2019 for the new rows coming out.<\/p>\n
Indeed, for this scenario, you would probably expect the output to reflect the \u2018state of the database\u2019 \u2013 the contents of each row \u2013 the way it existed at the moment you issued your query.<\/p>\n
One of the guarantees of the Oracle database is that \u201cthere are no dirty reads of user data\u201d. If necessary, Oracle will rebuild each row to the stable state at instant in time when the query was issued.<\/p>\n
Oracle does it nicely and very accurately through a \u201ctimer\u201d of its own, known as the System Change Number, AKA SCN.<\/p>\n
We will discuss these aspects of the SCN:<\/p>\n
So let\u2019s begin!<\/p>\n
In order to investigate SCN, several terms must be understood. Although these definitions are well documented, it is worth repeating them here:<\/p>\n
From the Concepts guide of Oracle online Documentation:<\/p>\n
\n\u201cA transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID\u201d.<\/p>\n<\/blockquote>\n
More simply: when we start a transaction we initiate a set of changes. These must be either completed in totality or must not be completed at all. As the transactions occur in the database, the users reading any area that has changed must not be affected from any side effects which can alter their results. Find more information in the Oracle INSERT, UPDATE, and DELETE guide<\/span><\/a>.<\/p>\n
In order for the transactions to be working correctly in any database, four ACID<\/strong> rules must be followed by the database. These are:<\/p>\n
\n
- Atomicity;<\/li>\n
- Consistency;<\/li>\n
- Isolation;<\/li>\n
- Durability (To see how other systems manage this, you can explore SQL Server transaction log concepts<\/b><\/a>)<\/li>\n<\/ul>\n
So where does SCN fit in all of this? And the answer of that question is that using SCN only Oracle maintains data consistency. This is done to ensure that any point of time, there is no discrepancy in the results that are shown to the others when one user is changing something, and vice-versa.<\/p>\n
In order to summarize, in Oracle, the principle of \u201cReaders do not wait for writers and writers don\u2019t need for readers\u201d is completely and truly followed. In order to do so, it is of the utmost importance that the data, which is currently undergoing any sort of change, must not be available to anyone except the person who is making those changes. For transactions, this is required to maintain the integrity of the data. Three things can occur which can disrupt this integrity \u2013 Dirty Read, Fuzzy Read and Phantom Read. To ensure that there won\u2019t be any sort of integrity issues in these transactions, different levels of Isolations are available. These are:<\/p>\n
\n
- Read Uncommitted<\/li>\n
- Read Committed<\/li>\n
- Repeatable Read<\/li>\n
- Serializable\u00a0<\/li>\n<\/ul>\n
Transaction Isolation levels<\/h2>\n
From these, Oracle offers Read Committed as the default<\/em> isolation level, making sure that there is no possibility of a user seeing the changes made by another user which are not yet committed. There must not be any read over that data which is marked as \u201cdirty\u201d and there must be a mechanism robust enough to make all this possible.<\/p>\n
To make this possible, SCN plays a vital role.<\/p>\n
SCN, an Introduction<\/h2>\n
System Commit Number may be understood as Oracle\u2019s representation of time. Oracle uses SCN for controlling consistency, for performing recovery and to order the change vectors in the redo.<\/p>\n
The SCN is found in many locations \u2013 both in memory and on the disk. Different locations are used to help the database system identify various states of the database. For example, in some locations it is used to denote the transaction\u2019s completion status and whether it is active or committed.<\/p>\n
Contrary to popular belief, it does not<\/strong> get generated only at the time of commit, though that\u2019s what the name itself suggests. SCN is there all the time in the database, representing a time portion for the operation which is happening at that instant of time. It is not completely inaccurate to say that SCN doesn\u2019t get generated with a commit, it does \u2013 but that\u2019s not the sole way SCN is generated.<\/p>\n
The SCN is a two-part number that is generated on request, in response to an event. It is somewhat like the date and time derived from a calendar and clock combination; the clock changes quickly and only when it has gone through the complete 24 hour cycle is the overflow \u2013 the calendar \u2013 changed. However, the event that changes the clock is the \u2018pendulum\u2019 and is regular, whereas the event that changes the SCN is a call to one specific internal function.<\/p>\n
Similar to a calendar\/clock timestamp, the value may be recorded in a variety of different places, each for a different use.<\/p>\n
One real-world example similar to an SCN is an airport clock. Two people enter an airport at the same time and may note the clock time. That moment gives us \u2018an entry SCN\u2019 which happens to be the same for both people. One person gets a cart for baggage before checking in, whereas the other proceeds directly to the check-in counter. When the seats are assigned, each person may get a different \u2018check-in SCN\u2019 since the times are slightly different. A \u2018boarding SCN\u2019 may be assigned to indicate when each has boarded, but both get the same \u2018take-off SCN\u2019.<\/p>\n
Like the combination of calendar and clock, the SCN is guaranteed to be increasing under normal operation. There is no guarantee that the number will be sequential (missing numbers).<\/p>\n
So where is this information about SCN used in the Oracle database? Well, almost everywhere. Just like we associate Time with every activity of ours, SCN is also associated with every part of database\u2019s functionality. For example, when you try to select data from a table, SCN is used to confirm that whether that data is consistent or not. SCN is also found in the data block\u2019s Transaction Headers. This SCN would represent the time when the transaction started and when it was committed. Similarly, for each change performed, an entry in the redo log is maintained and for each of these entries, SCN is used to represent the time of transaction\u2019s occurrence.<\/p>\n
Read consistency uses SCN to decide how long it has to apply Undo over the dirty buffer so that the read consistent data request for a session can be completed. And as is well known, SCN is incremented with every commit operation.<\/p>\n
SCN Format and Structure<\/h3>\n
SCN is a huge number with two components to it: SCN Base & SCB Wrap.<\/p>\n
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\u2019s increment and to ensure that the database won\u2019t run out of it. SCN_WRAP is incremented by 1 when SCN_BASE reaches the value of 4 Billion and SCN_BASE becomes 0.<\/p>\n
From Oracle Version 12c, the SCN number is an 8 byte number.<\/p>\n
So how do we see the current SCN value? The easiest way is to query the view V$DATABASE. Have a look:<\/p>\n
SQL> select current_scn from V$database;\n\nCURRENT_SCN\n-----------\n 1123790<\/pre>\nAs 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:<\/p>\n
SQL> select to_char('1123790','xxxxxxxx') scn_hex from dual;\n\nSCN_HEX\n---------\n 1125ce<\/pre>\nHere is an example of the output from the same view accessed couple of times:<\/p>\n
SQL> select current_scn from V$database;\n\nCURRENT_SCN\n-----------\n 1178883\n\nSQL> \/\n\nCURRENT_SCN\n-----------\n 1178885\n\nSQL> \/\n\nCURRENT_SCN\n-----------\n 1178886<\/pre>\nInterestingly, this increase in the SCN value shows an important aspect of SCN. We can see that with each execution, there is an increase in the count of the SCN. By querying the view V$DATABASE, we are actually causing the jump in the SCN number.<\/p>\n
SMON_SCN_TIME Table<\/h3>\n
The easiest way to see both of these values is from an internal table owned by SYS user- SMON_SCN_TIME. The following is an output from the same (11204).<\/p>\n
SQL> select SCN_wrp, SCN_bas, SCN from smon_SCN_time where rownum < 3;\n\n SCN_WRP SCN_BAS\t SCN\n---------- ---------- ----------\n\t 0 998222\t 998222\n\t 0 998406\t 998406<\/pre>\nThis table contains the entries of the SCNs generated. It stores the data in roughly 5-minute increments and holds 5 days\u2019 worth of data. This means the table contains approximately 1440 records. The exact number of records will vary slightly since the storage increment is not exactly 5 minutes.<\/p>\n
SQL> select count(*) from SMON_SCN_TIME;\n\n COUNT(*)\n----------\n 1494<\/pre>\nIn versions of Oracle prior to 10g, the time mapping of SCN with time was +\/- 5 minutes but from 10g onwards, this is changed to +\/- 3 seconds. As this is stores in an internal table, Oracle doesn\u2019t allow access to the information from this table directly. To access it, APIs are provided. One such API is from the package DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER, which can be used to access the sequence number from this table. An example of this is given below (thanks to Tom Kyte for the query):<\/p>\n
SQL> l\n\t select SCN_to_timestamp(SCN) ts, min(SCN), max(SCN)\n\t from (\n\t select dbms_flashback.get_system_change_number()-level SCN\n\t from dual\n\t connect by level <= 100\n\t\t )\n\t group by SCN_to_timestamp(SCN)\n*\t order by SCN_to_timestamp(SCN)\nSQL> \/\n\nTS\t\t\t\t\t\t\t\t\t MIN(SCN)\t MAX(SCN)\n--------------------------------------------------------------------------- ---------- ----------\n06-MAY-16 05.22.04.000000000 PM \t\t\t\t\t 1245323\t 1245323\n06-MAY-16 05.22.07.000000000 PM \t\t\t\t\t 1245324\t 1245324\n06-MAY-16 05.22.10.000000000 PM \t\t\t\t\t 1245325\t 1245325\n06-MAY-16 05.22.13.000000000 PM \t\t\t\t\t 1245326\t 1245326\n06-MAY-16 05.22.16.000000000 PM \t\t\t\t\t 1245327\t 1245327\n06-MAY-16 05.22.19.000000000 PM \t\t\t\t\t 1245328\t 1245328\n06-MAY-16 05.22.22.000000000 PM \t\t\t\t\t 1245329\t 1245329\n06-MAY-16 05.22.25.000000000 PM \t\t\t\t\t 1245330\t 1245330\n06-MAY-16 05.22.28.000000000 PM \t\t\t\t\t 1245331\t 1245331\n06-MAY-16 05.22.31.000000000 PM \t\t\t\t\t 1245332\t 1245332\n06-MAY-16 05.22.34.000000000 PM \t\t\t\t\t 1245333\t 1245333\n06-MAY-16 05.22.37.000000000 PM \t\t\t\t\t 1245334\t 1245334\n06-MAY-16 05.22.40.000000000 PM \t\t\t\t\t 1245335\t 1245335\n06-MAY-16 05.22.43.000000000 PM \t\t\t\t\t 1245336\t 1245336\n06-MAY-16 05.22.46.000000000 PM \t\t\t\t\t 1245337\t 1245337\n06-MAY-16 05.22.49.000000000 PM \t\t\t\t\t 1245338\t 1245338\n06-MAY-16 05.22.52.000000000 PM \t\t\t\t\t 1245339\t 1245339<\/pre>\nHow can we map SCN with Timestamp in versions prior to 10g?<\/strong><\/h3>\n
It\u2019s important to remember that this table doesn\u2019t show why<\/em> there would be any increase in the rate of increase in the SCN numbers. With a little formatting, it is possible to have an idea of the SCN numbers generated but the source of their growth won\u2019t be evident from this table.<\/p>\n
Another way to check this is from the view V$LOG_HISTORY. The view contains the SCN in the form of the columns FIRST_CHANGE# and NEXT_CHANGE# and we can see through these two columns the amount of SCNs generated in the database over a period of time. The \u201cfirst_change# is the lowest SCN that appears in the archived log file at a given sequence number of this thread. The \u201cnext_change#\u201d is the lowest SCN that appears in the next archived log file.<\/p>\n
SQL> select thread#, first_change#,next_change# from V$log_history;\n\n THREAD# FIRST_CHANGE# NEXT_CHANGE#\n---------- ------------- ------------\n\t 1\t 925702 958823\n\t 1\t 958823 959634\n\t 1\t 959634 972579\n\t 1\t 972579 993714\n\t 1\t 993714 1020785\n\t 1\t 1020785 1023738\n\t 1\t 1023738 1023873\n\t 1\t 1023873 1023995<\/pre>\nAs with SMON_SCN_TABLE it\u2019s not possible to find the source of the increase in the generation of the SCN numbers from this table view. Still, you can use this view in the single instance as well as in an RAC environment.<\/p>\n
SCN Increases Using a Sequence?<\/strong><\/h3>\n
By now it should be pretty evident that SCN appears to be a number that\u2019s constantly increasing. Interestingly, even though it\u2019s a number, Oracle doesn\u2019t use any sequence to increase it but uses internal functions instead. For example, to increase the SCN_BASE, the function used internally is KCMGAS(Get and Advance SCN). This function is called each time a new SCN is requested and the call for this function is increased. Similarly to this function, KCMGCS (Get Current SCN) is used to get the current SCN and the call used for it. These function calls can be seen from the V$sysstat view. A description of these statistics can be found in the 12.1 Reference guide<\/a>.<\/p>\n
Let\u2019s see how these calls link with the SCN generation. We are using two sessions here \u2013 one to see the calls in the view V$SYSSTAT view, and the other session to pull the SCN.<\/p>\n
Session -1 \nSQL> l\n 1* select current_SCN from V$database\nSQL> \/\n\nCURRENT_SCN\n-----------\n 698815\n\nSQL><\/pre>\nFor this SCN, these were the values for V$sysstat:<\/p>\n
SQL> l\n 1 select name,value from V$sysstat\n 2* where name like '%calls to%'\nSQL> \/\n\nNAME VALUE\n---------------------------------------------------------------- ----------\ncalls to kcmgcs 427\ncalls to kcmgrs 0\ncalls to kcmgas 7272\ncalls to get snapshot SCN: kcmgss 159790\n\nSQL><\/pre>\nLet\u2019s issue a query to see the current SCN in session 1:<\/p>\n
SQL> l\n 1* select current_SCN from V$database\nSQL> \/\n\nCURRENT_SCN\n-----------\n 698815\n\nSQL> \/\n\nCURRENT_SCN\n-----------\n 698889\n\nSQL><\/pre>\nAnd here is the result of the second session 2:<\/p>\n
SQL> \/\n\nNAME VALUE\n---------------------------------------------------------------- ----------\ncalls to kcmgcs 427\ncalls to kcmgrs 0\ncalls to kcmgas 7272\ncalls to get snapshot SCN: kcmgss 159790\n\nSQL> \/\n\nNAME VALUE\n---------------------------------------------------------------- ----------\ncalls to kcmgcs 427\ncalls to kcmgrs 0\ncalls to kcmgas 7278\ncalls to get snapshot SCN: kcmgss 159905\n\nSQL><\/pre>\nWe can see that the calls to the KCMGAS has increased to 7278 from the last value, 7272. As Oracle doesn\u2019t use a sequence to increase the number, there is no guarantee that the SCN number will always be increased in the same order.<\/p>\n
Conclusion<\/h2>\n