How does SCN_TO_TIMESTAMP work?

Question

Does the SCN itself encode a timestamp or is it a lookup from some table.

From an AskTom post he explains that the timestamp to +/-3seconds is stored in raw field in smon_scn_time. IS that where the function is going to get the value?

If so, when is that table purged if ever? If so, what triggers that purge?

If it is, does that make it impossible to translate old SCN's to Timestamps?

If it's impossible, then it eliminates any uses of that field that are long term things (read: auditing).

If I put that function in a query, would joining to that table be faster?

If so, anyone know how to covert that Raw column?

Answer 1

The SCN does not encode a time value. I believe it is an autoincrementing number.

I would guess that SMON is inserting a row into SMON_SCN_TIME (or whatever table underlies it) every time it increments the SCN, including the current timestamp.

I queried for the minimum recorded timestamp in several databases and they all go back about 5 days and have a little under 1500 rows in the table. So it is less than the instance lifetime.

I imagine the lower bound on how long the data is kept might be determined by the DB_FLASHBACK_RETENTION_TARGET parameter, which defaults to 1 day.

I would recommend using the function, they've probably provided it so they can change the internals at will.

No idea what the RAW column TIM_SCN_MAP contains, but the TIME_DP and SCN column would appear to give you the mapping.