Oracle 10g中的SCN与时间的映射及转换

Oracle 10g提供了两个新的函数对于SCN和时间戳进行相互转换，这两个函数是scn_to_timestamp和timestamp_to_scn，通过对SCN和时间戳进行转换，Oracle极大地方便了很多备份和恢复过程。

来看一下这两个函数的用法，首先通过dbms_flashback.get_system_change_number可以获得系统当前的SCN值：

SQL> col scn for 9999999999999
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
--------------
8908390522972

通过scn_to_timestamp函数可以将SCN转换为时间戳：

SQL> select scn_to_timestamp(8908390522972) scn from dual;
SCN
---------------------------------------------------------------------------
05-JAN-07 10.56.30.000000000 AM

再通过timestamp_to_scn可以将时间戳转换为SCN：

SQL> select timestamp_to_scn(scn_to_timestamp(8908390522972)) scn from dual; 
SCN
--------------
8908390522972

通过这两个函数，最终Oracle将SCN和时间的关系建立起来，在Oracle 10g之前，是没有办法通过函数转换得到SCN和时间的对应关系的（一般可以通过logmnr分析日志获得）。

但是需要注意的是这种转换要依赖于数据库内部的数据记录，对于久远的SCN则不能转换，请看以下举例：

SQL> select min(FIRST_CHANGE#) scn,max(FIRST_CHANGE#) scn from v$archived_log;
SCN                 SCN
------------------ ------------------
8907349093953       8908393582271

SQL> select scn_to_timestamp(8907349093953) scn from dual;
select scn_to_timestamp(8907349093953) scn from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
ORA-06512: at line 1

SQL> select scn_to_timestamp(8908393582271) scn from dual;
SCN
---------------------------------------------------------------------------
05-JAN-07 11.45.50.000000000 AM

在Oracle内部，这两个函数的时间转换正是依赖于SMON_SCN_TIME表，Oracle能够转换的最小SCN也就是这个表中记录的最小记录。

首先找到这个表中的最小SCN：

SQL> col scn for 9999999999999999
SQL> select scn_wrp*4294967296+SCN_bas scn from smon_scn_time
  2  where time_mp = (select min(time_mp) from smon_scn_time);
              SCN
-----------------
      18992916606

这个最小的SCN能够很容易被转换为时间戳：

SQL> select scn_to_timestamp(18992916606) from dual;
SCN_TO_TIMESTAMP(18992916606)
---------------------------------------------------------------------------
21-NOV-06 04.46.14.000000000 AM

即使比这个SCN小1的SCN也不能再转换，因为smon_scn_time表中已经没有了相应的映射关系：

SQL> select scn_to_timestamp(18992916605) from dual;
select scn_to_timestamp(18992916605) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
ORA-06512: at line 1

再来看看时间戳的转换，首先获得smon_scn_time表中记录的最小的时间戳：

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.

SQL> col scn for 99999999999999
SQL> select thread,time_mp,time_dp,scn_wrp,scn_bas,scn_wrp*65536*65536+scn_bas scn
  2  from smon_scn_time
  3  where time_mp = (select min(time_mp) from smon_scn_time);
    THREAD    TIME_MP TIME_DP                SCN_WRP    SCN_BAS             SCN
---------- ---------- ------------------- ---------- ---------- ---------------
         0  616513867 2007-03-06 13:51:07       2074 1573227590   8909335399494

这里的TIME_DP是能够转换的最小时间：

SQL> select timestamp_to_scn(
  2  to_timestamp('&time_mp', 'yyyy-mm-dd hh24:mi:ss')
  3  ) scn
  4  from dual;
Enter value for time_mp: 2007-03-06 13:51:07
old   2: to_timestamp('&time_mp', 'yyyy-mm-dd hh24:mi:ss')
new   2: to_timestamp('2007-03-06 13:51:07', 'yyyy-mm-dd hh24:mi:ss')
            SCN
---------------
  8909335399494

即使再小1秒，也是无法转换的：

SQL> /
Enter value for time_mp: 2007-03-06 13:51:06
old   2: to_timestamp('&time_mp', 'yyyy-mm-dd hh24:mi:ss')
new   2: to_timestamp('2007-03-06 13:51:06', 'yyyy-mm-dd hh24:mi:ss')
select timestamp_to_scn(
       *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

对于时间到SCN的转换，Oracle只能定位到3秒以内，继续以上的查询测试，3秒内的时间都被转换成同一个SCN：

SQL> /
Enter value for time_mp: 2007-03-06 13:51:08
old   2: to_timestamp('&time_mp', 'yyyy-mm-dd hh24:mi:ss')
new   2: to_timestamp('2007-03-06 13:51:08', 'yyyy-mm-dd hh24:mi:ss')
            SCN
---------------
  8909335399494

SQL> /
Enter value for time_mp: 2007-03-06 13:51:09
old   2: to_timestamp('&time_mp', 'yyyy-mm-dd hh24:mi:ss')
new   2: to_timestamp('2007-03-06 13:51:09', 'yyyy-mm-dd hh24:mi:ss')
            SCN
---------------
  8909335399494

SQL> /
Enter value for time_mp: 2007-03-06 13:51:10 
old   2: to_timestamp('&time_mp', 'yyyy-mm-dd hh24:mi:ss')
new   2: to_timestamp('2007-03-06 13:51:10', 'yyyy-mm-dd hh24:mi:ss')
            SCN
---------------
  8909335399500

最后总结一下，需要理解的是，Oracle的SCN和时间转换要依赖于SMON_SCN_TIME表中的映射关系，如果表中没有记录，则无法转换。也就是说，时间和SCN之间并不存在绝对的对应关系。