SCN on Scale of Time & SCN of a ROW

In the last post we have discussed about SCN structure and generation schemes, now we will see relation of SCN with timestamp and association with data populated in database tables.

SCN to TIMESTAMP: SCN is actually generated based on timestamp as well. So it’s easy to check timestamp from SCN and vice versa. Handy conversion features are available only from Oracle 10g onwards, but still from older versions Oracle had system object SYS.SMON_SCN_TIME, which can provide you the same information after some simple calculations.

In 10g onwards, there are two built-in functions to give the timestamp and SCN mapping details timestamp_to_scn() & scn_to_timestamp()

SQL> select scn_to_timestamp(current_scn-rownum) tm,
timestamp_to_scn(scn_to_timestamp(current_scn-rownum)) scn, current_scn-rownum
from v$database
connect by level <=5;

TM                                        SCN         CURRENT_SCN-ROWNUM
----------------------------------- ----------------- ------------------
14-AUG-12 01.25.59.000000000 AM               1274785            1274785
14-AUG-12 01.25.56.000000000 AM               1274784            1274784
14-AUG-12 01.25.51.000000000 AM               1274782            1274783
14-AUG-12 01.25.51.000000000 AM               1274782            1274782
14-AUG-12 01.25.48.000000000 AM               1274781            1274781


Oracle Documentation is saying that both functions actually trying to give approximate SCN or timestamp value. Here are the definitions:

TIMESTAMP_TO_SCN: takes as an argument a timestamp value and returns the approximate system change number (SCN) associated with that timestamp. The returned value is of datatype NUMBER. This function is useful any time you want to know the SCN associated with a particular timestamp.

SCN Structure & Generation Schemes

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 */