What is Oracle SCN?

System Change Number (SCN)?

The SCN is a stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN. The value of a SCN is the logical point in time at which changes are made to a database. The database uses these SCNs to query and track the changes. For example, if a transaction updates a row, then the database records the SCN at which this update occurred.

There is a very large upper limit to how many SCNs an Oracle Database can use. That limit is currently 281 trillion, or specifically 281,474,976,710,656 (is 2^48) where the Oracle Database should not run out of available ones.

SCN is a 6 Byte (48 bit) number represented by two parts РSCN_BASE and SCN_WRAP. An SCN_BASE is a 4 Byte (32 bit) number and SCN_WRAP is a 2 Byte (16 bit) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296 ), SCN_WRAP is increased by one and SCN_BASE will be reset to 0. This continues until SCN_WRAP reaches its maximum, i.e. 2 power 16 = 65536.

SCN = (SCN_WRAP * 4294967296) + SCN_BASE

When the base exceeds 4 billion, then the wrap is incremented by 1. Essentially, wrap counts the number of times base wrapped around 4 billion.

Logically, The maximum value of the wrap defines the maximum value of SCN i.e. maximum value of wrap*4 billion = 65536* 4* power(2,30) = 281474976710656 = 281 trillion values.

SCN is incremented with the completion of each and every transaction. A commit does not write to datafiles at all. It does not update control files at all.

The SCN is written to the controlfiles when the control files are updated – which happens as the result of a few things, one being “end of official checkpoint”.

SCN’s are written to redo logs continuously – when you commit they are emitted into the redo stream, and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED.

SCN Headroom

The difference between the current SCN the database is using, and the “not to exceed” upper limit, is known as the SCN headroom.

The current SCN can be obtained by either of the following queries:

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

To obtain SCN for a specific time: (Change the date and time to required detail)

SQL> col scn for 999999999999999999999999999999
select timestamp_to_scn(to_timestamp('06/06/2016 02:05:00','DD/MM/YYYY HH24:MI:SS')) as scn from dual;

Leave a Reply