Oracle SCN_TO_TIMESTAMP() Function

Oracle SCN_TO_TIMESTAMP() is a built-in function that returns an approximate timestamp associated with a given system change number (SCN).

SCN_TO_TIMESTAMP() is very useful in any situation where you need to know the timestamp associated with an SCN. For example, it can be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row.

Oracle SCN_TO_TIMESTAMP() Syntax

Here is the syntax for the Oracle SCN_TO_TIMESTAMP() function:

SCN_TO_TIMESTAMP(scn)

Parameters

scn

Required. The system change number (SCN).

Return Value

The Oracle SCN_TO_TIMESTAMP() function returns an approximate timestamp associated with the given system change number (SCN). The returned value is of the TIMESTAMP data type.

You cannot specify a NULL parameter, or Oracle will report an error.

Note:

  • The usual precision of the result value is 3 seconds.
  • The association between SCN and timestamp is remembered by the database for a limited time when the SCN is generated. This time period is the maximum of the automatic undo retention period in an automatic undo management mode database and the retention time of all flashback archives in the database, but not less than 120 hours. The association becomes outdated only after the database is opened. If the SCN specified for the SCN_TO_TIMESTAMP function parameter is too old, an error is returned.

Oracle SCN_TO_TIMESTAMP() Example

Here is an example that demonstrates the usage of the Oracle SCN_TO_TIMESTAMP() function.

The following statement creates a test table:

CREATE TABLE test (x varchar2(100));

The following statement inserts a row for testing:

INSERT INTO test (x)
VALUES ('x');

The following statement returns the system change number (SCN) of all rows in the test table and their corresponding timestamps:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';
SELECT
    ORA_ROWSCN,
    SCN_TO_TIMESTAMP(ORA_ROWSCN)
FROM test;

输出：

   ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
_____________ ________________________________
      9239115 2023-02-22 10:09:03.000000000

Conclusion

Oracle SCN_TO_TIMESTAMP() is a built-in function that returns an approximate timestamp associated with a given system change number (SCN).