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)
Coskan's Approach to Oracle 
Some related nice features are ORA_ROWSCN Pseudocolumn and AS OF Flashback Query;
DROP TABLE tt PURGE ;
CREATE TABLE tt (c1 NUMBER, c2 VARCHAR2(16), c3 DATE) ;
INSERT INTO tt VALUES (1, ‘A’, SYSDATE);
INSERT INTO tt VALUES (2, ‘B’, SYSDATE + 1);
COMMIT;
SELECT ORA_ROWSCN, a.* FROM tt a;
ORA_ROWSCN C1 C2 C3
———- ———- —————- ———-
1345542 1 A 09/03/2007
1345542 2 B 10/03/2007
UPDATE tt SET c1 = c1 + 1 WHERE c2 = ‘B’ ;
COMMIT;
SELECT ORA_ROWSCN, a.* FROM tt a;
ORA_ROWSCN C1 C2 C3
———- ———- —————- ———-
1345548 1 A 09/03/2007
1345548 3 B 10/03/2007
SELECT c1 FROM tt AS OF SCN 1345542 WHERE c2 = ‘B’;
C1
———-
2
DROP TABLE tt PURGE ;
CREATE TABLE tt (c1 NUMBER, c2 VARCHAR2(16), c3 DATE) ROWDEPENDENCIES ;
INSERT INTO tt VALUES (1, ‘A’, SYSDATE);
INSERT INTO tt VALUES (2, ‘B’, SYSDATE + 1);
COMMIT;
SELECT ORA_ROWSCN, a.* FROM tt a;
ORA_ROWSCN C1 C2 C3
———- ———- —————- ———-
1345618 1 A 09/03/2007
1345618 2 B 10/03/2007
UPDATE tt SET c1 = c1 + 1 WHERE c2 = ‘B’ ;
COMMIT;
SELECT ORA_ROWSCN, a.* FROM tt a;
ORA_ROWSCN C1 C2 C3
———- ———- —————- ———-
1345618 1 A 09/03/2007
1345625 3 B 10/03/2007
Comment by H.Tonguç Yılmaz — March 9, 2007 @ 9:16 pm
yeuvj uvqwcelt mnyfzc ocqd vjhyfn uomkpj jxar
Comment by dlkr vdkemhoi — September 8, 2008 @ 8:53 am
Any chance we can find SCN with respect to timestamp in 9i?
Comment by jcnars — December 27, 2010 @ 5:12 pm
Slightly flawed:-
select timestamp_to_scn(to_timestamp(’11-09-08 16:54:58′,’YY-MM-DD HH24:MI:SS’))as scn from dual;
SCN
——————
9669359567320
select scn_to_timestamp(9669359567320) as timestamp from dual;
TIMESTAMP
—————————————————————————
08-SEP-11 04.51.53.000000000 PM
Comment by AllanW — September 9, 2011 @ 12:03 pm
Hi,
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
Comment by vijay — January 11, 2013 @ 4:09 pm
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.
Thank you for sharing!
Comment by ogĹ‚oszenia drobne — May 15, 2014 @ 1:09 am
kiss 918
converting scn to a timestamp | Coskan’s Approach to Oracle
Trackback by kiss 918 — July 27, 2020 @ 1:38 am