eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

Oracle10g的current_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-


历史上的今天...
    >> 2013-06-21文章:
    >> 2011-06-21文章:
    >> 2006-06-21文章:

无觅

By eygle on 2007-06-21 10:07 | Comments (9) | Internal | 1471 |

9 Comments

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

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

我在我的测试库上查询都会看到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>

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


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

看到和导致是两者的区别:)

那就直接查询

select dbms_flashback.get_system_change_number current_scn from dual;

算了

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

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

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1933377

SQL> desc x$ktuxe;
Object x$ktuxe does not exist.

SQL> select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUX
------------------------------
1933373
SQL> 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

没有接触过9i以前版本的数据库,怎么后查询的SCN反而会比之前查询的SCN要小呢?



CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com