Oracle Study之--Oracle time AND scn convert

发布时间：2020-12-12 15:55:31 所属栏目：百科来源：网络整理

导读：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:

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!