4 Replies Latest reply: Sep 28, 2012 11:52 PM by 921598 RSS

    specific time SCN

    956880
      Hi experts,

      Can anyone tell me how to find the SCN number at specific time. We find the current SCN from v$database, but I need SCN number at specific seconds.
        • 1. Re: specific time SCN
          415289
          Try below query
          col SCN for 9999999999999999
          prompt Format --> DD/MM/YYYY HH24:MI:SS
          select timestamp_to_scn(to_timestamp('&ENTER_TIME','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
          • 2. Re: specific time SCN
            956880
            It is not correct it seems
            • 3. Re: specific time SCN
              Brian Bontrager
              SCN does not relate directly to a specific time. Oracle will approximate the point in time within 3 seconds.
              SQL> set time on
              13:04:23 SQL> col scn for 9999999999999999
              13:04:45 SQL> select current_scn from v$database;
              
                     CURRENT_SCN
              ------------------
                   2653841145028
              
              13:04:53 SQL> select timestamp_to_scn(to_timestamp('20120928130453','YYYYMMDDHH24MISS')) scn from dual;
              
                            SCN
              -----------------
                  2653841145027
              
              13:05:46 SQL> select timestamp_to_scn(to_timestamp('20120928130451','YYYYMMDDHH24MISS')) scn from dual;
              
              
                            SCN
              -----------------
                  2653841145027
              
              13:05:59 SQL>  select timestamp_to_scn(to_timestamp('20120928130450','YYYYMMDDHH24MISS')) scn from dual;
              
              
                            SCN
              -----------------
                  2653841145026
              
              13:06:05 SQL> select timestamp_to_scn(to_timestamp('20120928130454','YYYYMMDDHH24MISS')) scn from dual;
              
                            SCN
              -----------------
                  2653841145029
              
              13:06:12 SQL> select timestamp_to_scn(to_timestamp('20120928130453','YYYYMMDDHH24MISS')) scn from dual;
              
                            SCN
              -----------------
                  2653841145027
              In this example, the time range 13:04:51 - 13:04:53 all returns the SCN ending in ...027.
              13:04:54 returns the SCN ending in ...029.
              Note that the current_scn value in v$database at 13:53 (ending in ...028) is never returned by any of the timestamp_to_scn queries.

              This is a little easier to grasp looking at it the other way...
              13:06:18 SQL> select scn_to_timestamp('2653841145028') from dual;
              
              SCN_TO_TIMESTAMP('2653841145028')
              ---------------------------------------------------------------------------
              28-SEP-12 01.04.51.000000000 PM
              
              13:50:37 SQL> select scn_to_timestamp('2653841145027') from dual;
              
              SCN_TO_TIMESTAMP('2653841145027')
              ---------------------------------------------------------------------------
              28-SEP-12 01.04.51.000000000 PM
              
              13:50:54 SQL> select scn_to_timestamp('2653841145029') from dual;
              
              SCN_TO_TIMESTAMP('2653841145029')
              ---------------------------------------------------------------------------
              28-SEP-12 01.04.54.000000000 PM
              Here we see SCN's ...027 and ...028 both occurred within the same approximate 3-second window, which is consistent with the results we saw using timestamp_to_scn.
              • 4. Re: specific time SCN
                921598
                select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;