| SCN与TIMESTAMP的转换 | DBA之路：OoNiceDream |

SCN与TIMESTAMP的转换

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/05/scn_timestamp_map.html

Oracle 10G中，提供了scn_to_timestamp、timestamp_to_scn两个函数，方便了SCN与TIMESTAMP间的转换：

SQL> select dbms_flashback.get_system_change_number SCN from dual;
 
                SCN
-------------------
      9660154161187
 
SQL> select scn_to_timestamp(9660154161187) from dual;
 
SCN_TO_TIMESTAMP(9660154161187)
--------------------------------------------
17-MAY-09 03.22.49.000000000 PM
 
SQL> select timestamp_to_scn(
  2  to_date('2009-05-17 15:22:49','YYYY-MM-DD HH24:MI:SS')
  3  ) scn from dual;
 
                SCN
-------------------
      9660154161151
 
SQL>

另外，通过10G中的smon_scn_time表也增加了SCN列，可以方便地估出某个SCN对应的TIMESTAMP：

SQL> select * from 
  2  (select time_dp,scn_wrp,scn from smon_scn_time order by time_dp desc)
  3  where rownum < 5;
 
TIME_DP                SCN_WRP                 SCN
------------------- ---------- -------------------
2009-05-17 15:21:45       2249       9660154158593
2009-05-17 15:16:44       2249       9660154134394
2009-05-17 15:11:45       2249       9660154120829
2009-05-17 15:06:42       2249       9660154109194

而在Oracle 10G以前，Oracle没有提供scn_to_timestamp、timestamp_to_scn这两个函数，并且smon_scn_time也没有SCN列，不过针对smon_scn_time表，Oracle还是提供了SCN与TIMESTAMP的对应关系：

SQL> desc smon_scn_time
 Name         Null?    Type
 ------------ -------- -------------
 THREAD                NUMBER
 TIME_MP               NUMBER
 TIME_DP               DATE
 SCN_WRP               NUMBER
 SCN_BAS               NUMBER
 
SQL> select max(time_dp),max(scn_wrp),max(scn_bas) from smon_scn_time;
 
MAX(TIME_DP)        MAX(SCN_WRP) MAX(SCN_BAS)
------------------- ------------ ------------
2009-05-17 15:50:49         2500    824543959
 
SQL> select dbms_flashback.get_system_change_number SCN, sysdate from dual
 
            SCN SYSDATE
--------------- -------------------
 10738242784000 2009-05-17 15:52:46
 
SQL> select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN 
  2  from smon_scn_time where
  3  scn_bas='824543959';
 
TIMESTAMP                       SCN
------------------- ---------------
2009-05-17 15:50:49  10738242783959

此外，我们也可以借助v$log_history，v$archived_log等视图，估算SCN与TIMESTAMP的对应关系：

SQL> select * from
  2  (select SEQUENCE#,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#
  3   from  v$log_history order by sequence# desc)
  4  where rownum < 5;
 
 SEQUENCE#   FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#
---------- --------------- ---------------- ---------------
     45131  10751827094177 2009-05-17 15:09  10751830404860
     45130  10751823775963 2009-05-17 14:50  10751827094177
     45129  10751820671905 2009-05-17 14:31  10751823775963
     45128  10751817785732 2009-05-17 14:14  10751820671905
 
SQL> select * from
  2  (select SEQUENCE#,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#
  3   from  v$archived_log order by sequence# desc)
  4  where rownum < 5;
 
 SEQUENCE#   FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#
---------- --------------- ---------------- ---------------
     45131  10751827094177 2009-05-17 15:09  10751830404860
     45130  10751823775963 2009-05-17 14:50  10751827094177
     45129  10751820671905 2009-05-17 14:31  10751823775963
     45128  10751817785732 2009-05-17 14:14  10751820671905