Everything you wanted to know about SCNs but was too afraid to ask

So I did some reading about SCN's from various sources, i.e. mostly forums, blogs, and Tom Kyte's site. And there is A LOT of information out there about SCN, much more than would be of any practical use. And I don't want to reinvent the wheel, a lot of other people have explored the topic and are exponentially much more knowledgeable than I am. So I tried to compile the pertinent points which I found were most significant to my understanding about SCN's

What is it?

An SCN or System Change Number is an internal logical sequence assigned to transactions, used to provide a sequence to transactions and used to satisfy recovery and ACID requirements. (Doc ID 1376995.1)

A sequence? So it's like a ticket number given to each transaction?

Not necessarilly. One way to think of it as an internal timestamp. There is a very good analogy by Hans Forbrich in this forum post, where he compares it to the security camera at the airport. It captures data from multiple feeds, but are all linked together by the same timestamp. Say an auditor wanted to know what happened in the departure lounge, the check-in counter, and the luggage claim at 2:51 PM. The camera at each location would be different transactions within the database, and the timestamp of 2:51 PM would be the SCN.

But, then that would mean the same SCN would be assigned to multiple transactions?

YES! An SCN is NOT unique to a single transaction. Transactions in different sessions that commit at the same time CAN have the same SCN! (Doc ID 1376995.1)

So going back, you mentioned it's like a clock. Does that mean it is consistently moving?

Technically, no. An action is needed to move the SCN from it's current value to the next. This can be a transaction OR a query. Jonathan Lewis mentions that there are 3 actions that can cause the SCN to move forward, but I can't find the exact documentation that states this.
On the other hand, in a normally operating database it is always moving since Oracle background processes are always doing transactions, even if there are no user transactions being done. So in a way, it is consistently moving, but because of operations that are being performed that move it forward.

But when I query CURRENT_SCN from V$DATABASE, it keeps on increasing!

This is one of the operations that increments the SCN. As answered by Jonathan Lewis in this thread, when querying V$DATABASE it triggers an increment to the SCN. Oracle calls kcmgas (Get and Advance SCN) and kcmgss (Get Snapshot SCN). Side note - kcm stands for Kernel Cache Miscellaneous functions. Additionally, he also mentions that the functions dbms_cdc_utility.get_current_scn and dbms_flashback.get_system_change_number do not increase the SCN since they only call kcmgss.

I did a small experiment to try to prove this:

I have a short query that shows the stats for the session doing a select select on CURRENT_SCN, and the session doing a dbms_flashback.get_system_change_number.

select case when t.sid = 49 then 'dbms_flashback'
    else 'current_scn'
    end "OPERATION"
 ,
n.name, t.value
from v$statname n, v$sesstat t
where t.sid in ('42','49')
and t.statistic# in ('163','164')
and t.statistic# = n.statistic#
group by t.sid, n.name, t.value
order by t.sid;

OPERATION      NAME                                                    VALUE
-------------- -------------------------------------------------- ----------
current_scn    calls to get snapshot scn: kcmgss                          23
current_scn    calls to kcmgas                                             2
dbms_flashback calls to get snapshot scn: kcmgss                         319
dbms_flashback calls to kcmgas                                             1

Then ran the dbms_flashback function:

SQL> select dbms_flashback.get_system_change_number
  2  from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1172005

And then ran the query again:

OPERATION      NAME                                                    VALUE
-------------- -------------------------------------------------- ----------
current_scn    calls to get snapshot scn: kcmgss                          23
current_scn    calls to kcmgas                                             2
dbms_flashback calls to get snapshot scn: kcmgss                         320
dbms_flashback calls to kcmgas                                             1

As you can see the kcmgss stat for the dbms_flashback session increased by 1 to 320, while the rest of the stats remain unchanged.

In another session I then selected current_scn from v$database:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1172028

And then ran the query again;

OPERATION      NAME                                                    VALUE
-------------- -------------------------------------------------- ----------
current_scn    calls to get snapshot scn: kcmgss                          24
current_scn    calls to kcmgas                                             3
dbms_flashback calls to get snapshot scn: kcmgss                         320
dbms_flashback calls to kcmgas                                             1    

Reading about the SCN has pointed me in reading more about ITLs, which I had been putting off for a while. But for now it looks like a good topic for my next post.