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
