实验：Oracle的SCN增长间隔时间

Oracle的SCN增长间隔时间

查看Oracle数据库版本

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE11.2.0.1.0Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 

·查看当前scn，如下：

SQL>select dbms_flashback.get_system_change_number from dual
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
1155901
SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
1155934
 

·采用scn_to_timestamp获取scn与时间的对应关系如下：

SQL> select scn_to_timestamp(&scn) from dual;
Enter value for scn: 1155901
old1: select scn_to_timestamp(&scn) from dual
new1: select scn_to_timestamp(1155901) from dual
 
SCN_TO_TIMESTAMP(1155901)
---------------------------------------------------------------------------
05-APR-12 01.20.41.000000000 AM
 
SQL> /
Enter value for scn: 1155902
old1: select scn_to_timestamp(&scn) from dual
new1: select scn_to_timestamp(1155902) from dual
 
SCN_TO_TIMESTAMP(1155902)
---------------------------------------------------------------------------
05-APR-12 01.20.44.000000000 AM
 
SQL> select scn_to_timestamp(1155901) from dual;
 
SCN_TO_TIMESTAMP(2608762)
---------------------------------------------------------------------------
05-APR-12 01.20.41.000000000 AM
 

scn_to_timestamp以及timestamp_to_scn的相互转化：

SQL> select timestamp_to_scn(to_timestamp('05-APR-12 01.20.41.000000000 AM')) from dual;
 
TIMESTAMP_TO_SCN(TO_TIMESTAMP('05-APR-12 01.20.41.000000000 AM'))
---------------------------------------------------------------
1155901
 
SQL> select timestamp_to_scn(to_timestamp('05-04-12 01.20.41.000000000 AM')) from dual;
 
TIMESTAMP_TO_SCN(TO_TIMESTAMP('05-04-12 01.20.41.000000000 AM'))
---------------------------------------------------------------
1155901

SQL> select current_scn from v$database;

                CURRENT_SCN
---------------------------
                  293903296

SQL> select to_char(scn_to_timestamp(&scn),'YYYY-MM-DD HH24:MM:SS') from dual;
输入 scn 的值:  293903296
原值    1: select to_char(scn_to_timestamp(&scn),'YYYY-MM-DD HH24:MM:SS') from dual
新值    1: select to_char(scn_to_timestamp(293903296),'YYYY-MM-DD HH24:MM:SS') from dual

TO_CHAR(SCN_TO_TIME
-------------------
2014-02-19 18:02:49

SQL> /
输入 scn 的值:  293903296
原值    1: select to_char(scn_to_timestamp(&scn),'YYYY-MM-DD HH24:MM:SS') from dual
新值    1: select to_char(scn_to_timestamp(293903296),'YYYY-MM-DD HH24:MM:SS') from dual

TO_CHAR(SCN_TO_TIME
-------------------
2014-02-19 18:02:49