在Oracle中，SCN与时间如何相互转换？

DB宝 2020-04-14

3840

♣题目部分在Oracle中，SCN与时间如何相互转换？

     
♣答案部分

一个SCN值总是发生在某一个特定的时刻，只不过由于粒度的不一样，通常会存在多个SCN对应同一个时间戳的情况。Oracle 10g提供了两个新函数用于SCN和时间戳进行相互转换，这两个函数分别是SCN_TO_TIMESTAMP和TIMESTAMP_TO_SCN，通过对SCN和时间戳进行转换，极大地方便了Oracle的很多备份和恢复过程：
l SCN_TO_TIMESTAMP(scn_number)：将SCN转换成时间戳。
l TIMESTAMP_TO_SCN(timestamp)：将时间戳转换成SCN。
通过以上这两个函数，最终Oracle将SCN和时间的关系建立起来，在Oracle 10g之前，是没有办法通过函数转换得到SCN和时间的对应关系的，一般可以通过LogMiner分析日志获得。但是这两种函数转换要依赖于数据库内部表SMON_SCN_TIME，对于久远的SCN则不能转换。请看以下示例：
 1SYS@lhrdb> SELECT MIN(SCN) FROM SMON_SCN_TIME;
 2  MIN(SCN)
 3----------
 4   8622517
 5SYS@lhrdb> SELECT SCN_TO_TIMESTAMP(8622517) TIMESTAMP FROM DUAL;
 6TIMESTAMP
 7---------------------------------------------------------------------------
 808-OCT-16 04.30.26.000000000 AM
 9SYS@lhrdb> SELECT SCN_TO_TIMESTAMP(8622516) TIMESTAMP FROM DUAL;
10 SELECT SCN_TO_TIMESTAMP(8622516) TIMESTAMP FROM DUAL
11        *
12ERROR at line 1:
13ORA-08181: specified number is not a valid system change number
14ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

从上面的例子可以看出，Oracle能够转换的最小SCN也就是SMON_SCN_TIME的SCN列的最小值。
 1SYS@lhrdb> SELECT SCN_TO_TIMESTAMP(8763206) SCN FROM DUAL;
 2SCN
 3---------------------------------------------------------------------------
 410-OCT-16 05.22.40.000000000 PM
 5SYS@lhrdb> SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('10-OCT-16 05.22.40.000000000 PM','DD-Mon-RR HH:MI:SS.FF AM')) SCN FROM DUAL;
 6       SCN
 7----------
 8   8763206
 9SYS@lhrdb> SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2016-10-10 17:22:40','YYYY-MM-DD HH24:MI:SS')) SCN FROM DUAL;
10       SCN
11----------
12   8763206
13SYS@lhrdb> SELECT TO_CHAR(SCN_TO_TIMESTAMP(8763206), 'YYYY-MM-DD HH24:MI:SS') CHR_DATE,TIMESTAMP_TO_SCN(SCN_TO_TIMESTAMP(8763206)) DT FROM DUAL;
14CHR_DATE                    DT
15------------------- ----------
162016-10-10 17:22:40    8763206

对于时间到SCN的转换，Oracle只能定位到3秒以内，3秒内的时间都被转换成同一个SCN：
 1SYS@lhrdb> SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2016-10-10 17:22:40','YYYY-MM-DD HH24:MI:SS')) SCN FROM DUAL;
 2       SCN
 3----------
 4   8763206
 5SYS@lhrdb> SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2016-10-10 17:22:41','YYYY-MM-DD HH24:MI:SS')) SCN FROM DUAL;
 6       SCN
 7----------
 8   8763206
 9SYS@lhrdb> SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2016-10-10 17:22:42','YYYY-MM-DD HH24:MI:SS')) SCN FROM DUAL;
10       SCN
11----------
12   8763206
13SYS@lhrdb> SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2016-10-10 17:22:43','YYYY-MM-DD HH24:MI:SS')) SCN FROM DUAL;
14       SCN
15----------
16   8763213
17SYS@lhrdb> SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2016-10-10 17:22:39','YYYY-MM-DD HH24:MI:SS')) SCN FROM DUAL;
18       SCN
19----------
20   8763205

本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗