eygle.com   eygle.com
eygle.com  
 

« OU活动-挑战Oracle数据库管理大师（OCM） | Blog首页 | 《循序渐进Oracle》一书第一章目录 »

Oracle10g的current_scn是如何计算的?

作者：eygle |【转载时请务必以超链接形式标明文章和作者信息及本声明】
链接：
站内相关文章|Related Articles
  • Oracle10g中SCN与TimeStamp的相互转换
  • 如何获得当前数据库的SCN值
  • DB link与检查点(checkpoint)和SCN
  • Oracle10g v$database视图SCN增强
  • Oracle HowTo:如何更改数据库的SCN?

    • 前几天有一个朋友问我一个问题：
    Oracle10g的current_scn是如何计算的?

    我们知道Oracle10g在v$database视图中引入了current_scn，这个SCN来自底层表，代表当前的SCN，在Oracle9i中我们可以通过dbms_flashback.get_system_change_number来获得系统的SCN。

    但是注意current_scn还是有所不同的，我们看一下一个查询：

    [oracle@danaly ~]$ sqlplus '/ as sysdba'

    SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 21 10:15:08 2007

    Copyright (c) 1982, 2005, Oracle. All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

    SQL> @scn
    SQL> col current_scn for 99999999999999999
    SQL> select current_scn from v$database;

    CURRENT_SCN
    ------------------
    8910961765228

    SQL> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    ------------------
    8910961765228

    SQL> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    ------------------
    8910961765228

    SQL> select current_scn from v$database;

    CURRENT_SCN
    ------------------
    8910961765229

    SQL> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    ------------------
    8910961765229

    SQL> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    ------------------
    8910961765229

    SQL> select current_scn from v$database;

    CURRENT_SCN
    ------------------
    8910961765230

    我们看到current_scn的查询会直接导致SCN的增进，而其他方式并不会。也就是说在这里的current_scn就像是一个Sequence一样，查询会导致增进。这也很好理解，v$database只能通过增进当前的SCN才能保证获得的SCN是Current的。可是如果不查询呢？这个值肯定是不会增长的。

    也就是说你不查询就不知道current_scn的值，你查询它就变化，是不是有点向薛定谔的猫的那种感觉?

    -The End-

    By eygle on 2007-06-21 10:07 | Comments (7) | Posted to Internal | Edit |Pageviews:

    相关文章 随机文章
  • Oracle10g中SCN与TimeStamp的相互转换
  • 如何获得当前数据库的SCN值
  • DB link与检查点(checkpoint)和SCN
  • Oracle10g v$database视图SCN增强
  • Oracle HowTo:如何更改数据库的SCN?
    		•  EMC 错了么 之 C = 13 ?
    ChinaCache（北京蓝汛）招聘DBA
    Dopoda S1无法ActiveSync同步之问题解决
    在Linux下安装VNC 远程安装Oracle
    中国IT技术精英年会纪事之一
    网上相关主题:
    Google

    留言 (7)

    SCN查询也会增加,这有什么意义呢?请指点

    Posted by: yxyup at June 21, 2007 8:08 PM

    没有什么意义，一种处理方式而已，使得返回的SCN更加current
    :)

    Posted by: eygle at June 22, 2007 9:04 AM

    我在我的测试库上查询都会看到SCN的增进的呀
    一直都是这样的

    select dbms_flashback.get_system_change_number current_scn from dual;
    这样的查询也应该会看到SCN的增进呀
    SCN是一个不断调动的“时钟”
    只有不断的增进才符合的呀


    下面是我的测试
    ENV:10g R2 for Linux with ASM

    20:02:05 ixora10g@IXORA10G> select current_scn from v$database;

    CURRENT_SCN
    -----------
    1786814

    Elapsed: 00:00:00.00
    20:02:07 ixora10g@IXORA10G> select current_scn from v$database;

    CURRENT_SCN
    -----------
    1786816

    Elapsed: 00:00:00.00
    20:02:08 ixora10g@IXORA10G> select current_scn from v$database;

    CURRENT_SCN
    -----------
    1786818

    Elapsed: 00:00:00.00
    20:02:11 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786823

    Elapsed: 00:00:00.04
    20:02:26 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786823

    Elapsed: 00:00:00.00
    20:02:28 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786824

    Elapsed: 00:00:00.01
    20:02:30 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786827

    Elapsed: 00:00:00.01
    20:02:32 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786827

    Elapsed: 00:00:00.01
    20:02:33 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786827

    Elapsed: 00:00:00.00
    20:02:35 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786830

    Elapsed: 00:00:00.01
    20:02:44 ixora10g@IXORA10G> select dbms_flashback.get_system_change_number current_scn from dual;

    CURRENT_SCN
    -----------
    1786831

    Elapsed: 00:00:00.00
    20:02:48 ixora10g@IXORA10G> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for Linux: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

    Elapsed: 00:00:00.03
    20:03:03 ixora10g@IXORA10G>

    Posted by: ruochen0926 at June 26, 2007 8:01 PM

    >>我在我的测试库上查询都会看到SCN的增进的呀
    没说会不变，SCN当然会不断增加的...


    select dbms_flashback.get_system_change_number current_scn from dual;
    这样的查询也应该会看到SCN的增进呀
    >>是能看到增进，但不会导致增进

    看到和导致是两者的区别：）

    Posted by: eygle at June 27, 2007 1:31 PM

    那就直接查询

    select dbms_flashback.get_system_change_number current_scn from dual;

    算了

    Posted by: mictel_ace at August 19, 2007 8:43 AM

    能不能谈谈哪些事件会导致SCN改变呢?
    在什么资料好像说过:即使什么都不做,SCN也会每三秒钟递增,不知是否是真的?

    Posted by: lgtxzxm at September 17, 2007 11:18 PM

    看了上面的测试current_scn好像是每秒增进1，但dbms_flashback.get_system_change_number current_scn增进好像跟v$database里的current_scn不一样。到底它们两者是否同步的？

    Posted by: singlelove at January 28, 2008 11:25 AM

    发表留言:



    Remember Me?
    (输入验证码后方可评论,谢谢支持)


    CopyRight © 2004 eygle.com, All rights reserved.