11 August, 2020


A quick demo of SCN_TO_TIMESTAMP in 19c

 oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release - Production on Tue Aug 11 21:59:56 2020

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 10 2020 16:08:38 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release - Production

SQL> select scn_to_timestamp(5389994) from dual;

11-AUG-20 PM

SQL> select scn_to_timestamp(5389994-100000) from dual;

12-JUL-20 PM

SQL> select scn_to_timestamp(32720) from dual;
select scn_to_timestamp(32720) 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


If you query for an older SCN, you would get an ORA-08181 error.  What is an "older SCN" ?  

Technically, Oracle frequently inserts new rows into SYS.SMON_SCN_TIME and deletes older rows.  This is the table that is queried by the SCN_TO_TIMESTAMP function.  So, if you query for an SCN no longer present in the table, you get an ORA-08181 error.

Does Oracle insert every SCN into this table ? Of course not !  Else there would have been more 5million rows in the table in my database.  It periodically inserts rows.  When you run the SCN_TO_TIMESTAMP function, you get an approximate timestamp  -- an estimate that Oracle derives from reading "nearby" rows.  

Do not ever assume that SCN_TO_TIMETAMP returns an Exact Timestamp for that SCN.

For a range of potential SCNs, you can query V$ARCHIVED_LOG for FIRST_TIME (which is still in DATE format, not TIMESTAMP) and FIRST_CHANGE# (which is the first SCN recorded for that ArchiveLog).

No comments: