SCN Structure & Generation Schemes
November 3, 2014 6 Comments
We have discussed about SCN in our last post. Let’s explore about structure of SCN as well as generation/propagation schemes.
SCN Structure
SCN is a 6 Byte (48 bit) number with max value is 281,474,976,710,656 (281 Trillion) and represented as 2 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 * 2^32) + SCN_BASE
SQL> select max(scn_wrp),max(SCN_bas) from smon_scn_time group by scn_wrp;
MAX(SCN_WRP) MAX(SCN_BAS)
------------ ------------
0 2227514
Even if the SCN value does reach its maximum, then SCN will be reset to 0, thus causing a new incarnated database. So, all your old backups and archived logs become useless and you need to take fresh backups. From 12c onwards Oracle might use 8 Byte SCN format.
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;
- select scn_wrp*power(2,32) +SCN_bas SCN from smon_scn_time where scn_bas=( select max(scn_bas) SCN_BASE from sys.smon_scn_time); /* This query will show delayed output so only used for older releases where aforesaid methods will not work */
