都是时区惹的祸

想把时间戳转换为SCN的时候，出现ORA-08180: no snapshot found based on specified time报错
SCOTT@tstdb1-SQL> select timestamp_to_scn(to_date('20150513 20:00:00','yyyymmdd hh24:mi:ss')) from dual;
Select timestamp_to_scn(to_date('20150513 20:00:00','yyyymmdd hh24:mi:ss')) from dual
       *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

我们知道timestamp和scn之间的对应关系保存在sys.smon_scn_time表里，每5分钟记录一行，
SCOTT@tstdb1-SQL> select min(time_dp),min(scn) from sys.smon_scn_time;

MIN(TIME_DP)              MIN(SCN)
----------------- ----------------
20150513 16:45:19   12723373878322

要把timestamp转换成scn，这个timestamp至少要大于等于MIN(TIME_DP)，我们要转换的timestamp '20150513 20:00:00'满足这个条件，为何还会出现ORA-08180？

把上面得到的min(scn)转换为timestamp后发现与MIN(TIME_DP)标示的时间相差8小时
SCOTT@tstdb1-SQL> select scn_to_timestamp(12723373878322) from dual;

SCN_TO_TIMESTAMP(12723373878322)
---------------------------------------------------------------------------
14-MAY-15 12.45.19.000000000 AM

顿时明白了，sys.smon_scn_time里记录的时间其采用的是GMT时间，而我们数据库的时间是GMT+08:00
SCOTT@tstdb1-SQL> select dbtimezone from dual;

DBTIME
------
+08:00

对于GMT+08:00的数据来讲能够转换为SCN的timestamp应该大于sys.smon_scn_time里的min(time_dp)加上8小时
SCOTT@tstdb1-SQL> select min(time_dp)+interval '8' hour,min(scn) from sys.smon_scn_time;

MIN(TIME_DP)+INTE         MIN(SCN)
----------------- ----------------
20150514 00:45:19   12723373878322

大于等于20150514 00:45:19的timestamp才能够被转换成SCN，少1秒都不行
SCOTT@tstdb1-SQL> select timestamp_to_scn(MIN(TIME_DP)+to_dsinterval('0 7:59:59')) from sys.smon_scn_time;
select timestamp_to_scn(MIN(TIME_DP)+to_dsinterval('0 7:59:59')) from sys.smon_scn_time
       *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

SCOTT@tstdb1-SQL> select timestamp_to_scn(MIN(TIME_DP)+to_dsinterval('0 8:00:00')) from sys.smon_scn_time;

TIMESTAMP_TO_SCN(MIN(TIME_DP)+TO_DSINTERVAL('08:00:00'))
--------------------------------------------------------
                                          12723373878322

收到20150513 20:00:00不能被转换成SCN的报错也就顺理成章了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1664431/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1664431/