FB

Oracle SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents