Convert scn to timestamp / timestamp to scn

Trick to convert viceversa

ð  SCN to TIMESTAMP

ð  TIMESTAMP to SCN

 

Here is the process on how to convert SCN to TIMESTAMP and also TIMESTAMP to SCN in oracle database servers

In real time scenarios  we may get the requirement.

These are the seeded functions to convert SCN to Timestmp

SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN

 

Convert SCN to Timestamp:

 

SQL> select current_scn from v$database;

CURRENT_SCN
----------------------
   2835496

SQL> select scn_to_timestamp(2835496) from dual;

SCN_TO_TIMESTAMP(2835496)
---------------------------------------------------------------------------
28-FEB-21 11.15.09.000000000 AM

 

 

Convert TIMESTAMP to SCN:

 

SQL> select timestamp_to_scn(to_timestamp(‘28-FEB-21 11.15.09.000000000’,'dd-mm-yyyy hh24:mi:ss')) SCN from dual;

       SCN

----------------

   2835496