Oracle TIMESTAMP_TO_SCN() Function

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

TIMESTAMP_TO_SCN() is useful in any situation where you need to know the SCN associated with a specific timestamp.

Oracle TIMESTAMP_TO_SCN() Syntax

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

TIMESTAMP_TO_SCN(timestamp)

Parameters

timestamp

Required. The timestamp to use to determine the associated SCN.

Return Value

The Oracle TIMESTAMP_TO_SCN() function returns an approximate system change number (SCN) associated with a given timestamp. The return value is of NUMBER data type.

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

Oracle TIMESTAMP_TO_SCN() Example

The following example demonstrates the usage of the Oracle TIMESTAMP_TO_SCN() function.

We will use the example data from the Oracle SCN_TO_TIMESTAMP() function.

To get the system change number (SCN) associated with the timestamp '2023-02-22 10:09:03.000000000', use the following statement:

SELECT
    TIMESTAMP_TO_SCN('2023-02-22 10:09:03.000000000')
FROM test;

输出：

   TIMESTAMP_TO_SCN('2023-02-2210:09:03.000000000')
___________________________________________________
                                            9239114

Conclusion

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