How to convert SCN to TIMESTAMP or TIMESTAMP to SCN in oracle database

 

This document describes how to convert SCN to TIMESTAMP and TIMESTAMP to SCN in oracle database.

Sometimes we get the requirement convert SCN to Timestamp and Timestamp to SCN to recover the database until SCN or until Timestamp.

We use below two seeded functions to convert SCN to Timestamp.

SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN

Convert SCN to Timestamp:

SQL> select current_scn from v$database;

CURRENT_SCN
----------------------
    1772955

SQL> select scn_to_timestamp(1772955) from dual;

SCN_TO_TIMESTAMP(1772955)
---------------------------------------------------------------------------
20-FEB-21 12.13.06.000000000 AM

Convert TIMESTAMP to SCN:

SQL> select timestamp_to_scn(to_timestamp('20-02-2021 00:15:00','dd-mm-yyyy hh24:mi:ss')) SCN from dual;

       SCN

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

   1777489

 

Thanks for going through the document...........