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

Dealing with the "Stuck" Workflow Mailer in EBS 12.2: A Practical Guide

  We all have  seen something like this . We just finished a fresh clone from Production to Test. After the post-clone steps are done, the s...