Finding SCN by date

[ussupport1]

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

[Hein]

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