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

Datapatch Fails in Oracle 12c on Windows – Root Cause and Step-by-Step Fix

   Recently, while applying a patch on an Oracle 12c (12.2.0.1) database on Windows, I ran into a frustrating issue: datapatch kept failin...