Oracle10g的current_scn是如何计算的?

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

Oracle10g的current_scn是如何计算的?

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2007/06/oracle10g_current_scn.html

站内相关文章|Related Articles

使用ora_rowscn识别误操作数据时间点

Oracle Wait Events: Wait for scn ack

Oracle10g中SCN与TimeStamp的相互转换

如何获得当前数据库的SCN值

DB link与检查点(checkpoint)和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-

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

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

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

相关文章	随机文章
使用ora_rowscn识别误操作数据时间点 Oracle Wait Events: Wait for scn ack Oracle10g中SCN与TimeStamp的相互转换如何获得当前数据库的SCN值 DB link与检查点(checkpoint)和SCN	SUN与Oracle 新的蜜月期 Google的Web Clips终于支持自添加RSS DBA Scripts:获取用户创建语句 DBA警世录:DBA千万不要想当然花开无限好 3.8 有情天 - 龙胆花、跳舞兰

搜索本站:

留言 (9)

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

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要小呢？

Posted by: Asher at January 7, 2010 6:05 PM

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

dbms_flashback查询的是准确的。

Posted by: eygle at January 7, 2010 9:58 PM

www.eygle.com

Oracle10g的current_scn是如何计算的?

留言 (9)

发表留言: