SCN on Scale of Time & SCN of a ROW
November 4, 2014 6 Comments
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.
Read more of this post