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
