In this article, I’ll explain what an SCN is and how to use both the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN function.
Oracle SCN Explained: What is an SCN?
To understand what these two functions do, it helps to understand what an SCN is.
An SCN stands for System Change Number, and represents a point in time that a change has been made to the database. Every time a COMMIT is made to the database an SCN is created. It can then be used in FLASHBACK queries and to restore data to a certain point.
(SQL Developer has an auto-commit setting that you may want to check and modify. More information is available here.)
Purpose of the Oracle SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions
The SCN_TO_TIMESTAMP function converts one of these System Change Numbers (SCN) to a TIMESTAMP, which makes it easier to read and see when the change actually occurred.
Also, the opposite can also be done. The TIMESTAMP_TO_SCN function converts a TIMESTAMP value to an SCN. This is useful if you know the time of the change (and the date), but not the System Change Number.
Syntax and Parameters
The syntax of these functions are quite simple.
SCN_TO_TIMESTAMP ( scn )- scn (mandatory): The System Change Number to be converted to a TIMESTAMP.
TIMESTAMP_TO_SCN ( timestamp )- timestamp (mandatory): The TIMESTAMP value (date and time) to be converted to a System Change Number.
How Long is a Timestamp to SCN Mapping Kept?
The Oracle database stores the time that each SCN occurs, but it does not store this indefinitely.
According to Oracle, the period that this mapping is kept is:
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
So, if you try to run either of these queries on a timestamp that is too old and isn’t kept in this mapping, then an error will be returned.
ORA-00904: “scn_to_timestamp”: invalid identifier or ORA-00904: “timestamp_to_scn”: invalid identifier
If you try to run a query with the SCN_TO_TIMESTAMP function or TIMESTAMP_TO_SCN function, you might get one of these two errors:
ORA-00904: "scn_to_timestamp": invalid identifier ORA-00904: "timestamp_to_scn": invalid identifier
This error occurs because you’re attempting to get a timestamp or SCN for a period that is no longer kept in the Oracle database. As mentioned in the section above, there is only a certain period that this mapping is kept. If you try to obtain a value earlier than this period, you’ll get this ORA-00904 error.
ORA-08180: no snapshot found based on specified time
You may also get this error:
ORA-08180: no snapshot found based on specified time
This can also be caused by the same thing: attempting to get a SCN that is no longer stored in the database.
How Can I Find the Current SCN In My Database?
To find the current SCN, you can run this query:
SELECT
TO_CHAR(CURRENT_SCN) AS current_scn_value,
SCN_TO_TIMESTAMP(CURRENT_SCN) AS current_scn_timestamp
FROM v$database;Result:
| CURRENT_SCN_VALUE |
CURRENT_SCN_TIMESTAMP
|
| 405142 | 08/SEP/22 07:44:39.000000000 AM |
Examples of the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions
Here are some examples of these two functions.
Let’s start by viewing data in a table.
SELECT max_credit,
updated_date
FROM customer
WHERE customer_id = 41;| MAX_CREDIT | UPDATED_DATE |
| 100 | 18/FEB/22 |
Now, let’s update this value and COMMIT, which will generate a new SCN.
UPDATE customer
SET max_credit = 2000,
updated_date = SYSTIMESTAMP
WHERE customer_id = 41;
COMMIT;
SELECT max_credit,
updated_date
FROM customer
WHERE customer_id = 41;| MAX_CREDIT | UPDATED_DATE |
| 2000 | 08/SEP/22 |
Now, let’s take a look at the SCN that was generated when this row was updated.
SELECT max_credit,
updated_date,
ORA_ROWSCN
FROM customer
WHERE customer_id = 41;| MAX_CREDIT | UPDATED_DATE | ORA_ROWSCN |
| 2000 | 8/SEP/22 | 405242 |
We can see the timestamp of this SCN by using the SCN_TO_TIMESTAMP function.
SELECT max_credit,
ORA_ROWSCN,
SCN_TO_TIMESTAMP(ORA_ROWSCN) AS scn_time
FROM customer
WHERE customer_id = 41;| MAX_CREDIT | ORA_ROWSCN | SCN_TIME |
| 2000 | 405242 | 08/SEP/17 07:48:35.000000000 AM |
We can also use the TIMESTAMP_TO_SCN to find the SCN that was created when the updated_date was set:
SELECT max_credit,
TIMESTAMP_TO_SCN(updated_date) AS timestamp_scn
FROM customer
WHERE customer_id = 41;| MAX_CREDIT | TIMESTAMP_SCN |
| 2000 | 405239 |
So, that’s how you can use both the Oracle TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions.
They come in handy when working with FLASHBACK queries and seeing when data was last updated.
Similar Functions
Some functions which are similar to the TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions are:
- TO_TIMESTAMP: converts a string into a TIMESTAMP value.
- TO_DATE: converts a string to a DATE value.
If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.