数据库scn以及对应的时间查询

原创 Leo 2022-12-17

495

文档课题：数据库scn以及对应的时间查询.
--查某时间段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/