Oracle Study之--Oracle time AND scn convert 悠悠 2022-07-16 03:11 152阅读 0赞 **Oracle Study之--Oracle time AND scn convert** ***\[oracle@rh5 ~\]$ sqlplus '/as sysdba'*** SQL\*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 03:29:04 2016 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. 03:29:09 SYS@ prod>startup ORACLE instance started. Total System Global Area 393375744 bytes Fixed Size 1336764 bytes Variable Size 142608964 bytes Database Buffers 243269632 bytes Redo Buffers 6160384 bytes Database mounted. Database opened. **1、将SCN转换为TIME** **03:29:46 SYS@ prod>select current\_scn from v$database;** CURRENT\_SCN \----------- 884802 **03:30:04 SYS@ prod>select to\_char(scn\_to\_timestamp(884802),'yyyy-mm-dd hh24:mi:ss') from dual;** TO\_CHAR(SCN\_TO\_TIME \------------------- 2016-09-30 03:30:02 03:31:51 SYS@ prod>alter session set nls\_date\_format='yyyy-mm-dd hh24:mi:ss'; Session altered. 03:32:25 SYS@ prod>select sysdate from dual; SYSDATE \------------------- 2016-09-30 03:32:32 **2、将Time转换成SCN** **03:34:02 SYS@ prod>select timestamp\_to\_scn(to\_date('2016-09-30 03:32:32','yyyy-mm-dd hh24:mi:ss')) from dual;** TIMESTAMP\_TO\_SCN(TO\_DATE('2016-09-3003:32:32','YYYY-MM-DDHH24:MI:SS')) \---------------------------------------------------------------------- 884948 03:34:57 SYS@ prod>select current\_scn from v$database; CURRENT\_SCN \----------- 885016 ** 所谓的scn和timestamp的换算，其实是Oracle把他们之间的关系存储在了SYS.SMON\_SCN\_TIME中，但是里面的记录是有限的，超出了范围的，就查不了** **03:35:08 SYS@ prod>desc smon\_scn\_time Name Null? Type ----------------------------------------------------------------- -------- ------------------------- THREAD NUMBER TIME\_MP NUMBER TIME\_DP DATE SCN\_WRP NUMBER SCN\_BAS NUMBER NUM\_MAPPINGS NUMBER TIM\_SCN\_MAP RAW(1200) SCN NUMBER ORIG\_THREAD NUMBER**
相关 Oracle Study之--Oracle time AND scn convert Oracle Study之--Oracle time AND scn convert \[oracle@rh5 ~\]$ sqlplus '/as sysdba' 悠悠/ 2022年07月16日 03:11/ 0 赞/ 153 阅读
悠悠
还没有评论，来说两句吧...