ITPub博客

首页 > 数据库 > Oracle > 都是时区惹的祸

都是时区惹的祸

原创 Oracle 作者:oliseh 时间:2015-05-20 19:40:59 0 删除 编辑

想把时间戳转换为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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617042