How to convert SCN to timestamp and viceversa?

SCN_TO_TIMESTAMP(SCN) – Prints timestamp for the SCN value passed in
TIMESTAMP_TO_SCN(timestamp) – Prints SCN# for the timestamp value passed in.

To get the current SCN# one can query V$DATABASE and print the value of current SCN.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
—————-
50267271275

— get the current scn using timestamp_to_scn
SQL> select timestamp_to_scn(systimestamp) current_scn from dual;

CURRENT_SCN
—————-
50267273738

If the value passed in too old, you will get the following message “ORA-08180: Time specified is too old.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s