DB link与检查点(checkpoint)和SCN

昨天在测试的时候发现了一个有趣的现象，就是通过DB查询的2个数据库间，SCN会被同步。

测试的过程如下.
1.首先获得本地的SCN

    [oracle@jumper oracle]$ sqlplus "/ as sysdba"

    SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 7 21:07:56 2006

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    With the Partitioning option
    JServer Release 9.2.0.4.0 - Production

    SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual;

    SCN
    ----------
    5287824

2.通过db link进行SCN查询

    SQL> set serveroutput on
    SQL> set feedback off
    SQL> declare
      2  r_gname    varchar2(40);
      3  l_gname    varchar2(40);
      4  begin
      5     execute immediate
      6     'select GLOBAL_NAME from global_name@hsbill' into r_gname;
      7     dbms_output.put_line('gname of remote:'||r_gname);
      8     select GLOBAL_NAME into l_gname from global_name;
      9     dbms_output.put_line('gname of locald:'||l_gname);
     10  end;
     11  /
    gname of remote:HSBILL.HURRAY.COM.CN
    gname of locald:EYGLE

    SQL> declare
      2  r_scn      number;
      3  l_scn      number;
      4  begin
      5     execute immediate
      6     'select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER@hsbill from dual' into r_scn;
      7     dbms_output.put_line('scn of remote:'||r_scn);
      8     select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER into l_scn from dual;
      9     dbms_output.put_line('scn of locald:'||l_scn);
     10  end;
     11  /
    scn of remote:18992092078
    scn of locald:18992092078

我们可以看到，通过DB Link查询后，两个数据库的SCN被同步。
手工执行checkpoint，此时可以发现数据库的checkpoint scn被增进:

    SQL> col scn for 999999999
    SQL> select file#,CHECKPOINT_CHANGE# scn from v$datafile;

         FILE#               SCN
    ---------- -----------------
             1           5287605
             2           5287605
             3           5287605
             4           5287605

    SQL> alter system checkpoint;

    System altered.

    SQL> select file#,CHECKPOINT_CHANGE# scn from v$datafile;

         FILE#               SCN
    ---------- -----------------
             1       18992092162
             2       18992092162
             3       18992092162
             4       18992092162

这种机制其实是为了满足分布式事务(Distributed Transaction)的需要，只不过这里通过db link被触发。