Converting SCN to a Timestamp and Vice-versa

The system change number (SCN) is Oracle's clock, every time we commit the clock increments. The SCN just marks a consistent point in time in the database.Database SCN number some time is very important.

If you know the SCN (system change number) you can get its timestamp value (within about +/–3 seconds) by the function scn_to_timestamp.

*In versions of Oracle prior to 10g, the time mapping of SCN with time was +/- 5 minutes but from 10g onwards, this is changed to +/- 3 seconds.

• Filling gaps of archive logs between primary server and Physical standby server
• particularly when you are doing  recovery

GET_SYSTEM_CHANGE_NUMBER: for getting the current system change number of the database. in 9i

SQL>  select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1620762

SQL>

From 10g

SQL> select current_scn from v$database;  

CURRENT_SCN
-----------
    1621647


SQL>

SCN_TO_TIMESTAMP: for converting given scn to timestamp value.

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

TIMESTAMP
---------------------------------------------------------------------------
26-JAN-18 02.42.53.000000000 AM


TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamps value. You must use to_timestamp conversion for the
character value.


SQL> select timestamp_to_scn(to_timestamp('26/01/2018 02:42:53','DD/MM/YYYY HH24:MI:SS')) as scn from dual;

       SCN
----------
   1620762

SQL>


SQL> select timestamp_to_scn(systimestamp) current_scn from dual;

CURRENT_SCN
-----------
    1621155

SQL>



If the value passed in too old, you will get the following message "ORA-08180:no snapshot found based on specified time."


SQL> select timestamp_to_scn(to_timestamp('26/01/2012 02:42:53','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
select timestamp_to_scn(to_timestamp('26/01/2012 02:42:53','DD/MM/YYYY HH24:MI:SS')) as scn from dual
       *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1