How to Use Timestamp_To_Scn and Scn_To_Timestamp in Oracle

How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?

You're trying to look too far back. You can only convert to and from SCNs that are in the redo/flashback window maintained by your system. Once changes age out then the mapping is lost.

This is explained in the documentation:

The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.

Bear in mind these are part of Oracle's internal mechanism, and so are of limited use to us; though they are useful for flashback queries of course - again within the same window.

SCN to TimeStamp - wrong expression?

Please use

SELECT SCN_TO_TIMESTAMP(TIMESTAMP_TO_SCN(SYSTIMESTAMP)) FROM DUAL;

Last Updated record with timestamp

There is a limitation in Oracle 10g and 9i you can only flash back withing 5 days. In Oracle 11g there is no limitation.

You can check the oldest available System change number (SCN) number in database.

Using this query

select min(SCN) min_scn from sys.smon_scn_time;

Using the SCN NUMBER from the above query you can get the last TIMESTAMP for the SCN

SELECT SCN_TO_TIMESTAMP( scn number here) FROM dual;

If you check older SCN than minimum SCN then you will get the error.

source link

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

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"

Using the Oracle SCN keep track of modified rows

The main question I have is whether the SCN is strictly increasing,
i.e. whether the next generated SCN is always higher than the current
SCN

According to the documentation: ORA_ROWSCN Pseudocolumn

Whether at the block level or at the row level, the ORA_ROWSCN should
not be considered to be an exact SCN. For example, if a transaction
changed row R in a block and committed at SCN 10, it is not always
true that the ORA_ROWSCN for the row would return 10. While a value
less than 10 would never be returned, any value greater than or equal
to 10 could be returned. That is, the ORA_ROWSCN of a row is not
always guaranteed to be the exact commit SCN of the transaction that
last modified that row. However, with fine-grained ORA_ROWSCN, if two
transactions T1 and T2 modified the same row R, one after another, and
committed, a query on the ORA_ROWSCN of row R after the commit of T1
will return a value lower than the value returned after the commit of
T2.

If a block is queried twice, then it is possible for the value of
ORA_ROWSCN to change between the queries even though rows have not
been updated in the time between the queries. The only guarantee is
that the value of ORA_ROWSCN in both queries is greater than the
commit SCN of the transaction that last modified that row.

Getting max(ora_rowscn) - 10 minutes

You can convert to and from a timestamp with SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN:

select max(ora_rowscn),
  timestamp_to_scn(scn_to_timestamp(max(ora_rowscn))
    - interval '10' minute) as adj
from <your table>;

  MAX(ORA_ROWSCN)               ADJ
----------------- -----------------
   10320929886378    10320929885515

In some circumstances you might not be able to convert the SCN to a timestamp, if it's too old to look it up; then you'll get something like ORA-08181: specified number is not a valid system change number. Note the bit about SCN to timestamp association in the SCN_TO_TIMESTAMP documentation.

You could possibly bypass the max lookup and just use:

select timestamp_to_scn(systimestamp - interval '10' minute) from dual

... or using the end time of the load process. But you still might not have that association, depending on the time period and your redo retention.

A flashback query might be simpler here anyway, again assuming you're doing this inside the redo retention period:

select * from <your table>
as of timestamp (systimestamp - interval '10' minute);

... or as of any timestamp you want to specify.
Having a timestamp column on the table, set by your load process or automatically with a trigger, would be more reliable as it wouldn't rely on redo retention, but perhaps a little slower during the load itself. If this is a one-off check then a flashback query would be simplest though.

---

Related Topics