How to convert scn to a timestamp in Oracle

July 15, 2018
Oracle has inbuilt features using which you can convert scn to timestamp and timestamp to scn. SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. This function is useful any time you want to know the timestamp associated with an SCN. In order to do this, Oracle has provided two packages called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN.
convert-scn-to-timestamp
  • SCN is very important particularly when you are doing RMAN Recovery. 
  • Archivelog gap resolution and Recovery of Physical standby server.
  •  It can be used in an data pump export parameter file using FLASHBACK_SCN to ensure a consistent copy of the database at that point-in-time. 

HOW TO CONVERT SCN TO TIMESTAMP AND TIMESTAMP INTO SCN

Check the current scn of the database using below query.

SQL>select current_scn from v$database;

current_scn
--------------
4426538972

To get the timestamp value from the current scn number.

SQL> select scn_to_timestamp(4426538972) as timestamp from dual;

timestamp
-------------------------------
23-SEP-18 03.22.42.000000000 PM

To get the scn number from the timestamp.

SQL> select timestamp_to_scn(to_timestamp('23/09/2018 15:22:44','DD/MM/YYYY HH24:MI:SS')) as scn from dual;

SCN
----------
4426538972

Must Read: