I saw the nice function below while i was reading about locking and latches section of Mr Thomas Kytes Book (Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions) and i said to myself i have to share it for the ones like me🙂
If you know the SCN (system change number) you can get its timestamp value (within about +/–3 seconds) by the function scn_to_timestamp. After looking to the manual for more info i saw two other nice functions about scn. They are all under DBMS_FLASHBACK package and not available for the releases prior to 10g. I found these functions useful for dataguard issues recovery issues and flashback issues.
GET_SYSTEM_CHANGE_NUMBER: for getting the current system change number of the database.
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;
SQL> select scn_to_timestamp(884871) as timestamp from dual;
TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamp value. You must do to_timestamp convertion for the character value.
SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution Mr Thomas Kytes Book Pg 244