February 2, 2015

How to find out scn for a specific time


To get current scn:-

SQL> set time on
10:12:32 SQL> col scn for 9999999999999999
10:12:36 SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1208594

10:12:38 SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1208595


Now to find out scn for a specific time:-

SQL> select timestamp_to_scn(to_date('02/02/2015 10:12:38','mm/dd/yyyyhh24:mi:ss')) from dual;
TIMESTAMP_TO_SCN(TO_DATE('02/02/201510:12:38','MM/DD/YYYYHH24:MI:SS'))
----------------------------------------------------------------------
                                                               1208592
The usual precision of the result value is +/- 3 seconds.

No comments:

Post a Comment

Oracle Database 26ai Installation Using RPM on Oracle Linux 9 (OEL 9) – Step-by-Step Guide

  In this post I will describe the installation of Oracle Database 26ai 64-bit on Oracle Linux 9 (OL8) 64-bit. The installation requires a m...