How scn map to timestamp using sys.smon_scn_time in Oracle?

Question

In Oracle DB you can find out when your table was updated last time by using

SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from myTable; 

(see here)

Values in ora_rowscn column store in system change number (SCN) format.

Oracle knows what timestamp corresponds to particular scn using sys.smon_scn_time table:

As you can see there are scn, time_dp and some other columns in this table. Records add in sys.smon_scn_time every 5 minutes (approximately). So there is no single-valued relationship between scn and date. But Oracle knows timestamp for each scn.

So, how sys.smon_scn_time table work?

And how Oracle maps scn to timestamp and what accuracy of this mapping?

Answer 1

The tim_scn_mapcolumn is an array of timestamp to SCN mappings. Its size is RAW(1200). Each entry has 12 bytes. This makes 100 entries per row and this leads to an accuracy of around 3 seconds, since the SMON process creates a row every 300 seconds. See also the documentation of the scn_to_timestamp funtion.

The structure of a single mapping in the tim_scn_map column is:

• 4 bytes for time_mp
• 4 bytes for scn_bas
• 2 bytes for scn_wrp
• 2 bytes for "not yet known"