March 9, 2007

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.

Is there any limitation as to how far back the scn can be referred?
SQL> select scn_to_timestamp() from dual;
select scn_to_timestamp() from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at “SYS.SCN_TO_TIMESTAMP”, line 1

I got the scn value from STANDBY_BECAME_PRIMARY_SCN column of v$database

Hi there! This post could not bbe written any better!
Reading this post reminds me of my old room mate! He always kept chating aboout this.
I will forward this post to him. Fairly certain he wwill have a good read.