Converting SCN to a TimeStamp

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;

GET_SYSTEM_CHANGE_NUMBER
————————
884871
SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;

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

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

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;

SCN
———-
845396