Oracle SCN_TO_TIMESTAMP Function: A Simple Guide

The SCN_TO_TIMESTAMP function in Oracle SQL is a special function used to find the approximate timestamp associated with a given System Change Number (SCN).

An SCN is a critical internal Oracle number that acts like a database-wide clock, incrementing every time a transaction is committed. This function allows you to "translate" one of those SCN numbers back into a human-readable timestamp, which is invaluable for features like Flashback Query.

What is the SCN_TO_TIMESTAMP Function in Oracle?

The SCN_TO_TIMESTAMP(scn) function takes an SCN (a number) and returns the approximate TIMESTAMP when that SCN was generated by the database.

This is most commonly used with the ORA_ROWSCN pseudocolumn, which tells you the SCN of the last change made to a row. By combining SCN_TO_TIMESTAMP with ORA_ROWSCN, you can find out when a specific row was last updated.

Note: The association between an SCN and a timestamp is only stored for a limited time (e.g., a few days), which is controlled by your database's undo retention settings. You cannot use this function on very old SCNs.

SCN_TO_TIMESTAMP Function Syntax

The syntax for SCN_TO_TIMESTAMP is very simple:

SCN_TO_TIMESTAMP(scn_number)

Let's break that down:

• scn_number: The System Change Number (a NUMBER) you want to convert to a timestamp. This is often the ORA_ROWSCN value.

Oracle SCN_TO_TIMESTAMP Function Examples

Here are two practical examples of how to use SCN_TO_TIMESTAMP.

Example 1: Finding When a Row Was Last Updated using SCN_TO_TIMESTAMP

This is the most common use case. We can query the employees table to find an employee and also select the ORA_ROWSCN for that row. By wrapping it in SCN_TO_TIMESTAMP, we can see the time of the last change.

Query:

SELECT 
  last_name,
  salary,
  SCN_TO_TIMESTAMP(ORA_ROWSCN) AS "Last_Change_Time"
FROM 
  employees
WHERE 
  employee_id = 188;

Result: (The timestamp will vary based on when that row was last updated in your database.)

LAST_NAME                 SALARY Last_Change_Time
------------------------- ------ -----------------------------------
Telles                      3800 28-AUG-03 01.58.01.000000000 PM

Example 2: Finding the Timestamp of a Specific SCN using SCN_TO_TIMESTAMP

Imagine you have just committed a transaction and have its SCN (e.g., from an error log). You can use SCN_TO_TIMESTAMP to find out exactly when that commit happened.

(Note: We will use a placeholder SCN for this example. You cannot just invent an SCN, as it must be one your database recognizes.)

Query:

-- This query asks the database "What time corresponds to SCN 1234567?"
-- This will only work if 1234567 is a valid and recent SCN in your database.
SELECT 
  SCN_TO_TIMESTAMP(1234567) AS "Timestamp_of_SCN"
FROM DUAL;

Result: (The query would return the approximate timestamp for that SCN.)

Timestamp_of_SCN
-----------------------------------
07-NOV-25 11.18.30.000000000 AM