Coskan’s Approach to Oracle

March 9, 2007

converting scn to a timestamp

Filed under: How To, PL/SQL — coskan @ 2:58 pm

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

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution Mr Thomas Kytes Book Pg 244

Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 205 other followers