System Change Number (SCN)

The SCN is the key concept to ensure data consistency within a database.

The SCN is a number that is incremented when a transaction commits. The new SCN is then assigned to the transaction.

Each redo log block contains the first and last SCN of transactions in that block.

SCNs are also used to guarantee that replicated transactions are applied to the target database in the correct order.

An SCN cannot(?) be associated with a date prior to 1988-01-01.

Database links

The SCNs of databases that communicate on a database links are synchronized: the database with the lower SCN adjusts its SCN to the SCN of the database with the higher SCN.

Maximum physical value

As of 19c, the SCN is stored in 48 bits. Thus the maximum possible value for an SCN is 281,474,976,710,656 (=2⁴⁸ ≈ 2.8*10¹⁴). This is 281 trillion. If compatibility is set to 12.2, the number of bits is even increased to 2⁶³.

Maximum soft value

However, there is also a soft limit (aka RSL = reasonable SCN limit) which increases by n SCNs per second. The value of n is stored in the (hidden) parameter _max_reasonable_scn_rate:

select
   instVal.ksppstvl   instance_value
from
   x$ksppi   param                                             join
   x$ksppsv  instVal on  param.indx    = instVal.indx     and
                         param.inst_id = instVal.inst_id
where
   param.inst_id = userenv('instance')          and
   param.ksppinm = '_max_reasonable_scn_rate';

Headroom

The SCN headroom is the difference between the RSL and the actual SCN.

TODO: The (hidden) parameter _low_scn_headroom_warning_threshold_days (low SCN headroom warning threshold in days) seems to indicate that is possible to be warned when head room becomes tight.

Views where the SCN is found

In the dynamic performance views (v$…) and the data dictionary, the columns that store SCNs seem to have the form %CHANGE%#. Thus, they can be queried with

select distinct
   viw.view_name,
   viw.column_name,
-- viw.table_name,
   com.comments
from (
    select 
       col.owner,
       regexp_replace(
       regexp_replace(
       regexp_replace(col.table_name, '^AWR_[CP]DB_', 'AWR_xDB_')
                                    , '^G?V_\$'     , 'V$'      )
                                    , '^(DBA|CDB)_' , 'DBA_'    ) view_name,
       col.table_name,
       col.column_name
   from
      dba_tab_columns col  join
      dba_objects     obj on col.owner      = obj.owner      and
                             col.table_name = obj.Object_name
   where
      obj.object_type not in ('SYNONYM') and
      col.column_name like '%CHANGE%#'
)                  viw                                left join
dba_col_comments   com on viw.owner       = com.owner       and 
                          viw.table_name  = com.table_name  and
                          viw.column_name = com.column_name
order by
   viw.view_name,
   viw.column_name

Queries

select
   dbms_flashback.get_system_change_number
from
   dual;

The SCN in v$database

The current SCN is found in current_scn in gv$database. This view also provides some other interesting events that are associated with an SCN:

select
   db_unique_name,
   dbid,
   current_scn,
   checkpoint_change#,
   archive_change#,
   controlfile_change#,
   archivelog_change#
from
   gv$database;

Find the SCN in the fixed tables

The current value of the SCN can also be found in x$ tables.

First, we need to get the address of the memory location where the SCN is stored. x$ksmfsv exhibits a list of pointers that point to values. We're interested in kcsgscn_:

select
   addr                                      addr_of_pointer,
   ksmfsadr                                  addr_of_SCN_value_hex,
   to_number(ksmfsadr, rpad('x', 16, 'x'))   addr_of_SCN_value_int,
   ksmfssiz,
   ksmfstyp
from
   x$ksmfsv  fixed_variables
where
   ksmfsnam = 'kcsgscn_' -- SCN
;

With this address, we can query x$ksmmem, a fixed table which gives direct access to the memory of the fixed SGA:

select
   to_number(ksmmmval, 'XXXXXXXXXXXXXXXX') as current_scn
from
   x$ksmmem
where
   addr=hextoraw('00007FF718801D20');

I wanted to join these two select statements but the join crashed the session.

The SCN is also exposed in x$kccdi (kernel cache database information?), the same fixed table that also v$database queries from:

select
   dicur_scn as current_SCN
from
   x$kccdi;

Use oradebug

With oradebug, the value of the SCN might be found like so

oradebug setmypid
oradebug dumpvar sga kcsgscn