Get Data Moving 1
Results 1 to 2 of 2

Thread: Finding SCN by date

  1. #1
    ussupport1 is offline US Support
    Join Date
    Sep 2006
    Posts
    49
    Rep Power
    0

    Finding SCN by date

    In our Oracle RAC support we use an agent_context and a context that is converted due to the "Use Merger" feature that is reqired. This causes a reset of the CDC to be soley via an SCN number instead of a date and time. The query below will allow you to generate the SCN via a date and time. So for instance, if you need to reset the CDC solution back six hours you could use the query to find the SCN that was generated six hours ago.


    SQL> select timestamp_to_scn(to_date('12/18/2011 12:01:00','mm/dd/yyyy hh24:mi:s
    s')) from dual;

    TIMESTAMP_TO_SCN(TO_DATE('12/18/201112:01:00','MM/DD/YYYYHH24:MI:SS'))
    ----------------------------------------------------------------------
    7945494132
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    164
    Rep Power
    13

    finding date for SCN

    Just for sake of completeness, the inverse operation is also easy:


    SQL> select GROUP#, SEQUENCE#, FIRST_CHANGE# from v$log;

    GROUP# SEQUENCE# FIRST_CHANGE#
    ---------- ---------- -------------
    1 55 2131225
    2 56 2143977
    3 54 2116256

    SQL> select scn_to_timestamp('2131225') from dual;

    SCN_TO_TIMESTAMP('2131225')
    ----------------------------------------------------------
    22-DEC-11 11.24.59.000000000 PM

    SQL>

    hth,
    Hein

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •