convert scn to a timestamp

How to convert scn to a timestamp in Oracle

by

Oracle includes built-in features that allow you to convert scn into timestamp or timestamp to SCN. SCN_TO_TIMESTAMP accepts as an argument a number which evaluates to a system number (SCN) and returns the approximate timestamp associated. This function can be used to find the approximate timestamp associated an SCN. Oracle provides two packages, TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP. convert scn to a timestamp.

  • When you are working on RMAN Recovery, SCN is crucial.
  • Archivelog gap resolution, Recovery of Physical standby servers
  • You can use it in a FLASHBACK_SCN data pump export parameter file to maintain a consistent copy at any given point-in time.

Convert SCN to a Timestamp

Follow the below steps.

How to find the Current SCN number

Use the below query to find the current SCN number.

SQL>select current_scn from v$database;

OR

SQL>select to_char(current_scn) SCN from v$database;

SCN
--------------
9799126476110

Check Timestamp Against SCN

Using the below query you can find out the timestamp against the SCN.

SQL> select scn_to_timestamp(9799126476110) as timestamp from dual;

TIMESTAMP
-----------------------------------------
15-JUN-22 01.56.33.000000000 AM

Find the SCN against the timestamp

Find SCN against timestamp using the below query:

SQL>select timestamp_to_scn(to_timestamp('15-JUN-22 01.56.33','DD-MON-YY HH24:MI:SS')) AS scn from dual;

SCN
----------------------------------------
9799126464266
Convert SCN to a Timestamp

Read more from the rider

Leave a Comment