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

[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，建立新的数据缓存。