文档课题:数据库scn以及对应的时间查询.标签:12,15,16,数据库,查询,scn,2022,dd From: https://blog.51cto.com/u_12991611/5949484
--查某时间段scn号
select scn, to_char(time_dp + interval '8' hour, 'yyyy-mm-dd hh24:mi:ss')
from sys.smon_scn_time
where to_char(time_dp + interval '8' hour, 'yyyy-mm-dd hh24:mi:ss') >
'2022-12-15 00:00:00'
and to_char(time_dp + interval '8' hour, 'yyyy-mm-dd hh24:mi:ss') <
'2022-12-15 23:09:00'
order by scn;
……
SCN TO_CHAR(TIME_DP+INT
---------- -------------------
2823514 2022-12-15 16:28:08
2823965 2022-12-15 16:33:23
2844410 2022-12-15 16:41:33
2844814 2022-12-15 16:46:49
2844923 2022-12-15 16:51:38
2845398 2022-12-15 16:57:01
2846078 2022-12-15 16:57:11
2846433 2022-12-15 17:02:52
2847894 2022-12-15 17:03:16
2848034 2022-12-15 17:08:14
2848179 2022-12-15 17:13:32
……
说明:
a、 若scn无法显示,可用科学计数法col scn format 9999999999
b、 加8小时是因为所在时区为东8区
--查当前scn及对应时间
SYS@orcl> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;
TO_CHAR(SCN_TO_TIME CURRENT_SCN
------------------- -----------
2022-12-15 16:59:05 2846170
--scn转为时间
SYS@orcl> select to_char(scn_to_timestamp(2846170),'yyyy-mm-dd hh24:mi:ss') scn from dual;
SCN
-------------------
2022-12-15 16:59:05
--时间转为scn
SYS@orcl> select timestamp_to_scn(to_timestamp('2022-12-15 16:59:05','YYYY-MM-DD HH24:MI:SS')) scn from dual;
SCN
----------
2846166
SYS@orcl> select to_char(scn_to_timestamp(2846166),'yyyy-mm-dd hh24:mi:ss') scn from dual;
SCN
-------------------
2022-12-15 16:59:05
参考文档:http://blog.itpub.net/31547066/viewspace-2375513/