oracle19c>sqlplus hemant/hemant@orclpdb1 SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 11 21:59:56 2020 Version 19.3.0.0.0 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 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select scn_to_timestamp(5389994) from dual; SCN_TO_TIMESTAMP(5389994) --------------------------------------------------------------------------- 11-AUG-20 09.53.44.000000000 PM SQL> SQL> select scn_to_timestamp(5389994-100000) from dual; SCN_TO_TIMESTAMP(5389994-100000) --------------------------------------------------------------------------- 12-JUL-20 11.19.13.000000000 PM SQL> 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 SQL>
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:
Post a Comment