Skip to Main Content
  • Questions
  • How to calculate timestamp from an old ora_rowsn taking as a reference a current ora_rowscn timestamp_to_scn(sysdate) from DUAL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, José María.

Asked: October 13, 2021 - 11:00 am UTC

Last updated: October 21, 2021 - 2:26 am UTC

Version: 18xe

Viewed 100+ times

You Asked

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.

and Connor said...

Does exist any relationship between the above MAX(ORA_ROWSCN) from MY_TABLE and the current ORA_ROWSCN from sysdate?

No because that table might not have been updated in years.

If you cannot convert an SCN to a timestamp directly, then probably the closest you can get is by looking at v$log_history and v$archived_log which will have a timestamp and start/end scns entries in there. This will give you an estimate as to what time the scn 99464620 corresponds to.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database