As you know, the association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited time period and an error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.
Example:
select scn_to_timestamp(max(ora_rowscn)) from MY_TABLE
ORA-08181: el número especificado no es un número de cambio del sistema válido
ORA-06512: en "SYS.SCN_TO_TIMESTAMP", línea 1
08181. 00000 - "specified number is not a valid system change number"
*Cause: supplied scn was beyond the bounds of a valid scn.
*Action: use a valid scn.Nevertheless, I can obtain the max(ora_rowscn) from MY_TABLE without any kind of error:
select max(ora_rowscn) from MY_TABLE
MAX(ORA_ROWSCN)
---------------
99464620
I would like to obtain the corresponding timestamp of this ORA_ROWSCN approximately (I don't care if it may vary some hours),
without using any AUDIT tool but using PL/SQL statements.
Does exist any relationship between the above MAX(ORA_ROWSCN) from MY_TABLE and the current ORA_ROWSCN from sysdate?
select timestamp_to_scn(sysdate), TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') from dual
TIMESTAMP_TO_SCN(SYSDATE) TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss')
------------------------- ----------------------------------------
116631465 13-10-2021 12:12:02
Could I calculate, using any kind of algorithm, the corresponding timestamp to
99464620 value?
Thanks in advance.