4 Replies Latest reply: Apr 7, 2009 6:09 PM by 694352 RSS

    obtaining scn

    694352
      hi
      Is it possible to see the scn at a particular time?
      For example what is the scn at 19:30 two days ago.
      I have already checked alert log.but it is not written in there
        • 1. Re: obtaining scn
          JustinCave
          Depending on the Oracle version and how exact you need to be
          SQL> select timestamp_to_scn( trunc(sysdate-2) + 19.5/24 )
            2    from dual;
          
          TIMESTAMP_TO_SCN(TRUNC(SYSDATE-2)+19.5/24)
          ------------------------------------------
                                            15789184
          is probably what you're looking for.

          Justin
          • 2. Re: obtaining scn
            694352
            thanks Justin
            I didnt get what "19.5" used for.

            can u give me one more example:
            For instance I want to see the scn at 31 march 2009 18:27
            • 3. Re: obtaining scn
              JustinCave
              19.5 is the number of hours since midnight on the day in question (you indicated that you wanted 2 days ago at 19:30, so you get midnight two days ago and add 19.5 hours).

              TIMESTAMP_TO_SCN takes a date (or something that can be cast to a date) so you can be explicit
              SQL> ed
              Wrote file afiedt.buf
              
                1  select timestamp_to_scn( to_date('3-APR-2009 18:27:00',
                2                                   'DD-MON-YYYY HH24:MI:SS') )
                3*   from dual
              SQL> /
              
              TIMESTAMP_TO_SCN(TO_DATE('3-APR-200918:27:00','DD-MON-YYYYHH24:MI:SS'))
              -----------------------------------------------------------------------
                                                                             15658729
              But Oracle doesn't retain the SCN to timestamp mapping forever. If you try to go back to March 31, you'll probably get an error
              SQL> ed
              Wrote file afiedt.buf
              
                1  select timestamp_to_scn( to_date('31-MAR-2009 18:27:00',
                2                                   'DD-MON-YYYY HH24:MI:SS') )
                3*   from dual
              SQL> /
              select timestamp_to_scn( to_date('31-MAR-2009 18:27:00',
                     *
              ERROR at line 1:
              ORA-08180: no snapshot found based on specified time
              ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1
              Justin
              • 4. Re: obtaining scn
                694352
                hmmm
                Thanks Justin.
                I guess I cant see the scn at that time cos, it is not written in alert log as well