3 Things You Wish You’ve Learned In School About Oracle’s SCN

There are things you don’t learn in school.
There are things you hear about in school, that are not explored in-depth.
There are things you learn in school and then you forget about.

What about the System Change Number or SCN? Where does that fit for you?

I can hear the next statement in your head, already coming…Well, every DBA should know what the SCN number is, otherwise they are not a DBA!

What we should or shouldn’t know it doesn’t really matter. What really matters is what we DO know now, and what we do with what we know.

For me, the System Change Number was taught in school, however I did not study it in-depth. It just occurred to me, that we take this SCN number for granted, without knowing much about it. We use the terminology all the time, and if we were to explain it to someone who has no clue of Oracle, we wouldn’t be able to.

That is why I came up with today’s post, to deepen your knowledge of the System Change Number.

1. What Is SCN?
2. When Is the SCN Updated?
3. How Can I Find The Current SCN And More?

1. What Is SCN?

The SCN or the System Change Number is a memory location in the SGA (System Global Area). The SCN is considered to be the database’s clock, a counter.
A specific SCN number identifies a committed version of the database at a point in time.
Based on Oracle’s documentation the maximum number for SCN is 281474976710656 = 281 trillion (this was for 11gR2, for 12c couldn’t find the SCN number yet)

2. When Is The SCN Updated?

Any process that can access the SGA, can read and update the SCN.
Processes read the current value of the SCN at the start of each query or transaction, and at the end of a transaction, if a commit occurs, the process will increment the SCN.
There are 2 internal routines that do these readings and updates:

• kcmgss – Get Snapshot SCN
• kcmgas – Get And Advance SCN

The SCN is not only updated when users issue commits. The background processes are always doing something, thus they will also increment the SCN.

3. How Can I Find The Current SCN?

There are multiple ways to get the current SCN number. I am providing you 2 ways below.
Running both statements one after the other, will most likely generate different numbers, as the database is busy performing work, it never sleeps (unless you shut it down).

select dbms_flashback.get_system_change_number scn from dual; 
select current_scn from v$database;

SQL> select dbms_flashback.get_system_change_number scn from dual;

                                    SCN
---------------------------------------
                           304209761994

SQL> select current_scn from v$database;

                            CURRENT_SCN
---------------------------------------
                           304209761995

How can I translate the SCN into a timestamp and back?

Let me ask that differently:

Is there a way to find out what was the SCN number on a specific date and time? The answer is yes: use the timestamp_to_scn function.
Is there a way to find out what was the date and time for a specific SCN number? The answer is yes: use scn_to_timestamp function.

See below some examples, to better understand this:

SQL >SELECT SCN_TO_TIMESTAMP(304209761995) as scn_timestamp from dual;

SCN_TIMESTAMP
---------------------------------------------------------------------------
17-JAN-18 09.58.58.000000000 PM

SQL >SELECT TIMESTAMP_TO_SCN('17-JAN-18 09.58.58.000000000 PM') as timestamp_scn from dual;

                                TIMESTAMP_SCN
---------------------------------------------
                                 304209761989

Did you notice something interesting? You would think the 2 values are identical. I am running these queries in the same database, version 11.2.0.4, and providing the same timestamp. Notice the SCN numbers are slightly different. The reason is, these functions return the “approximate” SCN value.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana