How does SCN_TO_TIMESTAMP work?
The SCN itself encodes the timestamp or looks for it from some table.
From the AskTom post, he explains that the timestamp up to +/- 3 seconds is stored in a raw field at smon_scn_time. IS where the function will get the value?
If so, when is this table cleared, if ever? If so, what triggers are clearing?
If so, does that make it impossible to convert the old SCN to Timestamps?
If this is not possible, then this precludes any use of this field that is long term (read: audit).
If I put this function in a query, would the join to this table be faster?
If so, does anyone know how to hide this Raw column?
SCN does not encode time value. I believe this is an auto-incrementing number.
I would suggest that SMON inserts a row into SMON_SCN_TIME (or whatever table underlies) every time it increments the SCN, including the current timestamp.
I have queried the minimum recorded timestamp across multiple databases and they all return about 5 days and have just under 1500 rows in the table. Thus, it is less than the lifetime of the instance.
I suppose the lower bound on how long data is retained can be determined by the DB_FLASHBACK_RETENTION_TARGET parameter, which defaults to 1 day.
I would recommend using a function, they probably provided it so that they can modify the internals at will.
Don't know what the RAW TIM_SCN_MAP column contains, but the TIME_DP and SCN column will be mapped for display.
source to share