Oracle10g的current_scn是如何计算的?

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

前几天有一个朋友问我一个问题：
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文章:

东北大学、西财、兰州商学院-2013 ACOUG高校行琐记

>> 2011-06-21文章:

Oracle数据恢复：文件数据错误(循环冗余检查) 解决

>> 2006-06-21文章:

IBM DB2 9 (Viper)版本新特性体验

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

9 Comments

yxyup | June 21, 2007 8:08 PM

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

eygle | June 22, 2007 9:04 AM

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

ruochen0926 | June 26, 2007 8:01 PM

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

eygle | June 27, 2007 1:31 PM

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

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

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

mictel_ace | August 19, 2007 8:43 AM

那就直接查询

select dbms_flashback.get_system_change_number current_scn from dual;

算了

lgtxzxm | September 17, 2007 11:18 PM

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

singlelove | January 28, 2008 11:25 AM

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

Asher | January 7, 2010 6:05 PM

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;

TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

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

eygle | January 7, 2010 9:58 PM

查询X$的信息是计算出来的，不是当前的SCN

dbms_flashback查询的是准确的。