想把时间戳转换为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/