[20210104]单实例data buffer states 2.txt

--//对于前面的测试做一些必要的补充。

1.环境：
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ cat bh.sql
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b# state
--------------------------------------------------------------------------------

col object_name format a20
col state format a10

select
b.inst_id,
b.hladdr,
  b.dbarfil,
  b.dbablk,
  b.class,
  decode(b.class,1,'data block',2,'sort block',3,'save undo block', 4,
  'segment header',5,'save undo header',6,'free list',7,'extent map',
  8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
  12,'bitmap index block',13,'file header block',14,'unused',
  15,'system undo header',16,'system undo block', 17,'undo header',
  18,'undo block') class_type,
  decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated') as state,
  b.tch,
  cr_scn_bas,
  cr_scn_wrp,
  cr_uba_fil,
  cr_uba_blk,
  cr_uba_seq,
  ba,
  b.LE_ADDR,
  (select object_name from dba_objects where data_object_id = b.obj) as object_name
from x$bh b
where
  dbarfil = &1 and
  dbablk = &2
;

2.测试：
--//session 1:
SCOTT@book> select rowid from dept where deptno=10;
ROWID
------------------
AAAVRCAAEAAAACHAAA

SCOTT@book> @ rowid AAAVRCAAEAAAACHAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     87106          4        135          0  0x1000087           4,135                alter system dump datafile 4 block 135 ;

--//session 2:
SYS@book> set verify off
SYS@book> @ bh 4 135
no rows selected

--//现在没有查询到信息是正常的，我前面的查询仅仅输出rowid，通过主键索引就可以定位，并没有访问对应的数据块dba=4,135.
--//session 1:
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         xcur                1          0          0          0          0          0 0000000078B0A000 00               DEPT

--//session 1:
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA' for update;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         xcur                2          0          0          0          0          0 000000006D904000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  2  425228666          3          0          0          0 0000000078B0A000 00               DEPT

--//session 3:
SCOTT@book> select CURRENT_SCN,dept.* from dept,v$database where dept.rowid='AAAVRCAAEAAAACHAAA';
CURRENT_SCN     DEPTNO DNAME          LOC
----------- ---------- -------------- -------------
 1.3310E+10         10 ACCOUNTING     NEW YORK

SCOTT@book> set numw 12
SCOTT@book> select CURRENT_SCN,dept.* from dept,v$database where dept.rowid='AAAVRCAAEAAAACHAAA';
 CURRENT_SCN       DEPTNO DNAME          LOC
------------ ------------ -------------- -------------
 13310130825           10 ACCOUNTING     NEW YORK

--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         cr                  1  425228935          3          3       4163      18733 0000000062B34000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  1  425228929          3          3       4163      18733 000000007A30E000 00               DEPT
         1 0000000084D25320          4        135          1 data block         xcur                2          0          0          0          0          0 000000006D904000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  2  425228666          3          0          0          0 0000000078B0A000 00               DEPT

--//查询特定的scn看看。
--//3,425228932 = scn(10): 13310130820 = scn(16): 0x319587a84
--//session 3:
SCOTT@book> select * from dept as of scn 13310130820 where deptno=10;
      DEPTNO DNAME          LOC
------------ -------------- -------------
          10 ACCOUNTING     NEW YORK

--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         cr                  2  425228935          3          3       4163      18733 0000000062B34000 00               DEPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         1 0000000084D25320          4        135          1 data block         cr                  1  425228929          3          3       4163      18733 000000007A30E000 00               DEPT
         1 0000000084D25320          4        135          1 data block         xcur                2          0          0          0          0          0 000000006D904000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  2  425228666          3          0          0          0 0000000078B0A000 00               DEPT
--//注意看tch列。也就是通过scn=3,425228935回退到3,425228932(13310130820).
--//session 3:
SCOTT@book> select * from dept as of scn 13310130820 where deptno=10;
      DEPTNO DNAME          LOC
------------ -------------- -------------
          10 ACCOUNTING     NEW YORK

--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         cr                  3  425228935          3          3       4163      18733 0000000062B34000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  1  425228929          3          3       4163      18733 000000007A30E000 00               DEPT
         1 0000000084D25320          4        135          1 data block         xcur                2          0          0          0          0          0 000000006D904000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  2  425228666          3          0          0          0 0000000078B0A000 00               DEPT

--//可以flashback query并没有生成占用数据缓存，而是通过某个scn反向rollback到特定scn。
--//session 3,增加scn到13310130840.
SCOTT@book> select * from dept as of scn 13310130840 where deptno=10;
      DEPTNO DNAME          LOC
------------ -------------- -------------
          10 ACCOUNTING     NEW YORK

--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         cr                  1  425228952          3          3       4163      18733 0000000060C92000 00               DEPT
         1 0000000084D25320          4        135          1 data block         xcur                2          0          0          0          0          0 000000006D904000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  3  425228935          3          3       4163      18733 0000000062B34000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  1  425228929          3          3       4163      18733 000000007A30E000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  2  425228666          3          0          0          0 0000000078B0A000 00               DEPT
--//增加1行。

--//session 1:

SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         cr                  1  425228952          3          3       4163      18733 0000000060C92000 00               DEPT
         1 0000000084D25320          4        135          1 data block         xcur                3          0          0          0          0          0 000000006D904000 00               DEPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        
         1 0000000084D25320          4        135          1 data block         cr                  3  425228935          3          3       4163      18733 0000000062B34000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  1  425228929          3          3       4163      18733 000000007A30E000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  2  425228666          3          0          0          0 0000000078B0A000 00               DEPT
--//state=xcur ,tch增加。

3.继续测试：
--//做一个特殊查询：

SCOTT@book> select CURRENT_SCN,dept.* from dept as of scn 13310130860,v$database where dept.rowid='AAAVRCAAEAAAACHAAA';
 CURRENT_SCN       DEPTNO DNAME          LOC
------------ ------------ -------------- -------------
 13310131535           10 ACCOUNTING     NEW YORK
--//注意CURRENT_SCN是当前的scn。
--//session 2:
SYS@book> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 0000000084D25320          4        135          1 data block         cr                  1  425228972          3          3       4163      18733 0000000062B7E000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  1  425228952          3          3       4163      18733 0000000060C92000 00               DEPT
         1 0000000084D25320          4        135          1 data block         xcur                3          0          0          0          0          0 000000006D904000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  3  425228935          3          3       4163      18733 0000000062B34000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  1  425228929          3          3       4163      18733 000000007A30E000 00               DEPT
         1 0000000084D25320          4        135          1 data block         cr                  2  425228666          3          0          0          0 0000000078B0A000 00               DEPT
6 rows selected.
--//3,425228972 = scn(10): 13310130860 = scn(16): 0x319587aac

总结：
--//1. as of scn ,从数据缓存找scn大于查询时的scn，rollback到新记录。
--//2  as of scn ,如果数据缓存找scn小于查询时的scn，建立新的数据缓存。