[20201231]单实例data buffer states.txt

来源：这里教程网时间：2026-03-03 16:21:13 作者：

[20201231]单实例data buffer states.txt --//别人问的一个问题PI是什么状态，PI表示past image.是rac环境特有的状态，不会出现在单实例的数据库中。 --//既然提到这个问题，今天就是探究一下单实例data buffer states。 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# -------------------------------------------------------------------------------- col object_name format a20 col state format a10 select 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, (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> @ 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 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block xcur 1 0 0 0 0 0 0000000076D08000 DEPT --//STATE=xcur，tch=1. --//session 3: SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block xcur 2 0 0 0 0 0 0000000076D08000 DEPT --//STATE=xcur,TCH=2,只要session 3的执行与前面间隔3秒，就出现tch增加。 3.继续测试： --//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 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block xcur 2 0 0 0 0 0 000000007484C000 DEPT 0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT --//注意看ba 数据块地址，可以发现原来的STATE变成了cr。而新增加1个缓存，state=xcur.也就是当前状态。 --//session 1,再次执行查询呢？ SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT --//注意看ba以及tch字段，tch增加到2.可以发现实际上访问的是BA=000000007484C000 的数据缓存。 --// session 3,如果这个时候访问该块呢？ SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT --//增加一个数据缓存，state=CR,也就是通过state=xcur通过undo构造出来的数据缓存块。 --// session 3,如果再次执行呢？ SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT --//再次增加1个数据缓存块。当然不会无限增加下去，受隐含参数_db_block_max_cr_dba的控制。 SYS@book> @ hide _db_block_max_cr_dba NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD -------------------- -------------------------------------------- ------------- ------------- ------------ ----- --------- _db_block_max_cr_dba Maximum Allowed Number of CR buffers per dba TRUE 6 6 FALSE FALSE --//session 3: SCOTT@book> select dbms_flashback.get_system_change_number,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA'; GET_SYSTEM_CHANGE_NUMBER DEPTNO DNAME LOC ------------------------ ---------- -------------- ------------- 1.3310E+10 10 ACCOUNTING NEW YORK SCOTT@book> set numw 12 SCOTT@book> select dbms_flashback.get_system_change_number,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA'; GET_SYSTEM_CHANGE_NUMBER DEPTNO DNAME LOC ------------------------ ------------ -------------- ------------- 13309689283 10 ACCOUNTING NEW YORK --//13309689283 = scn_wrap,scn_base(10): 3,424787395 = scn_wrap,scn_base(16): 0x3,0x1951bdc3 --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787384 3 3 620 18543 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT 6 rows selected. --//注意看CR_SCN_BAS列，实际上仅仅与上面的查询 dbms_flashback.get_system_change_number相差1。 --//3,424787394 = scn(10): 13309689282 = scn(16): 0x31951bdc2 --//session 3,访问scn=3,424787384时的状态呢？ --//3,424787384 = scn(10): 13309689272 = scn(16): 0x31951bdb8 SCOTT@book> select * from dept as of scn 13309689272 where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ------------ -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2： SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT --//注意看下划线那行，可以发现并没有增加记录，而该行TCH变成2. 4.继续测试: --//session 3,继续执行： SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ------------ -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 6 rows selected. --//session 3,继续执行： SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ------------ -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424787951 3 3 620 18543 0000000073ADE000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 7 rows selected. --//session 3,继续执行： SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ------------ -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424787987 3 3 620 18543 0000000073EE4000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787951 3 3 620 18543 0000000073ADE000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 7 rows selected. --//可以发现STATE=CR的行不再增加，删除了CR_SCN_BAS最小的那行。 5.如果脏块写盘呢？ --//session 2: SYS@book> alter system checkpoint; System altered. --//可以执行多次,避免IMU的影响。 SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424787987 3 3 620 18543 0000000073EE4000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787951 3 3 620 18543 0000000073ADE000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT 0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT 7 rows selected. --//可以发现state并没有变化。将脏块写盘，可以发现state状态不会改变。 --//刷新数据缓存呢？ SYS@book> alter system flush BUFFER_CACHE; System altered. SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- -------------------- 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073EE4000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073ADE000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073AB8000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 000000007484C000 DEPT 7 rows selected. --//flush BUFFER_CACHE后，state全表变成free。 6.继续： --//session 1，注意我没有提交: SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- -------------------- 0000000084D25320 4 135 1 data block xcur 1 0 0 0 0 0 0000000074290000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073EE4000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073ADE000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073AB8000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 000000007484C000 DEPT 8 rows selected. SYS@book> alter system flush BUFFER_CACHE; System altered. SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- -------------------- 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000074290000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073EE4000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073ADE000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073AB8000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073A82000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073D5C000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 00000000738D0000 DEPT 0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 000000007484C000 DEPT 8 rows selected. --//session 3，换成session 3执行呢？ SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA'; DEPTNO DNAME LOC ------------ -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT 0000000084D25320 4 135 1 data block xcur 0 0 0 0 0 0 00000000748DE000 DEPT --//可以发现产生2条记录，先从数据文件读取块在通过undo构造新的块，出现2行记录。 --//session 3 SCOTT@book> select CURRENT_SCN,dept.* from dept,v$database where dept.rowid='AAAVRCAAEAAAACHAAA'; CURRENT_SCN DEPTNO DNAME LOC ------------ ------------ -------------- ------------- 13309691673 10 ACCOUNTING NEW YORK --//13309691673 = scn_wrap,scn_base(10): 3,424789785 = scn_wrap,scn_base(16): 0x3,0x1951c719 SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424789783 3 3 620 18543 0000000077012000 DEPT 0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT 0000000084D25320 4 135 1 data block xcur 0 0 0 0 0 0 00000000748DE000 DEPT --//3,424789783 = scn(10): 13309691671 = scn(16): 0x31951c717 --//通过访问v$database的CURRENT_SCN字段，可以发现增加2.注：访问v$database视图的CURRENT_SCN每次访问都会递增1.大家可以自行测试。 7.补充测试： --//session 1,注意我的会话中的事务没有提交。 SCOTT@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ 10.4.82650 C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ---------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 4 82650; 10 4 82650 3 620 18543 1 ACTIVE 1 1 0A000400DA420100 0000000081CE1568 2020-12-31 09:00:14 3587 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 620; SYS@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$'; System altered. ******************************************************************************** Undo Segment: _SYSSMU10_1197734989$ (10) ******************************************************************************** Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271 last map 0x00000000 #maps: 0 offset: 4080 Highwater:: 0x00c0026c ext#: 2 blk#: 108 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 2 Unlocked Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x00c00111 length: 7 0x00c000c8 length: 8 0x00c00200 length: 128 0x00c00b00 length: 128 Retention Table ----------------------------------------------------------- Extent Number:0 Commit Time: 1609369220 Extent Number:1 Commit Time: 1609369220 Extent Number:2 Commit Time: 1609369220 Extent Number:3 Commit Time: 1609369220 TRN CTL:: seq: 0x486f chd: 0x0001 ctl: 0x001d inc: 0x00000000 nfb: 0x0000 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00c0026c.486f.01 scn: 0x0003.1951b4d0 --//0x0003.1951b4d0 = scn(10): 13309686992 = scn(16): 0x31951b4d0 Version: 0x01 FREE BLOCK POOL:: uba: 0x00000000.486f.26 ext: 0x2 spc: 0x2f6 uba: 0x00000000.486f.49 ext: 0x2 spc: 0x420 uba: 0x00000000.486f.03 ext: 0x2 spc: 0x1760 uba: 0x00000000.2ec6.04 ext: 0x2 spc: 0x1e02 uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0 TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x142e6 0x000e 0x0003.1951b61e 0x00c00263 0x0000.000.00000000 0x00000003 0x00000000 1609375975 0x01 9 0x00 0x142f3 0x0011 0x0003.1951b4d6 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375949 0x02 9 0x00 0x142fd 0x0009 0x0003.1951b545 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375975 0x03 9 0x00 0x142ed 0x0000 0x0003.1951b566 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375975 0x04 10 0x80 0x142da 0x0002 0x0003.1951b736 0x00c0026c 0x0000.000.00000000 0x00000001 0x00000000 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0x05 9 0x00 0x142f0 0x001a 0x0003.1951b759 0x00c0026b 0x0000.000.00000000 0x00000001 0x00000000 1609376224 .... 0x20 9 0x00 0x142dd 0x0007 0x0003.1951b656 0x00c00269 0x0000.000.00000000 0x00000003 0x00000000 1609375975 0x21 9 0x00 0x13d82 0x0016 0x0003.1951b4de 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375949 --// 0x00c0026c= set dba 3,620 = alter system dump datefile 3 block 620 = 12583532 --// 0x3.1951b736 = scn(10): 13309687606 = scn(16): 0x31951b736 --// 13309687606 = scn_wrap,scn_base(10): 3,424785718 = scn_wrap,scn_base(16): 0x3,0x1951b736 SYS@book> SELECT * FROM x$bh WHERE dbarfil=4 AND dbablk = 135 and state=1 2 @ prxx ============================== ADDR : 00007F7120754080 INDX : 7139 INST_ID : 1 HLADDR : 0000000084D25320 BLSIZ : 8192 NXT_HASH : 0000000084D260C0 PRV_HASH : 00000000743F6BA8 NXT_REPL : 00000000844F5630 PRV_REPL : 00000000787DD128 FLAG : 33554433 FLAG2 : 0 LOBID : 0 RFLAG : 0 SFLAG : 0 LRU_FLAG : 0 TS# : 4 FILE# : 4 DBARFIL : 4 DBABLK : 135 CLASS : 1 STATE : 1 MODE_HELD : 0 CHANGES : 1 CSTATE : 0 LE_ADDR : 00 DIRTY_QUEUE : 0 SET_DS : 00000000844F5148 OBJ : 87106 BA : 00000000748DE000 CR_SCN_BAS : 0 CR_SCN_WRP : 0 CR_XID_USN : 0 CR_XID_SLT : 0 CR_XID_SQN : 0 CR_UBA_FIL : 0 CR_UBA_BLK : 0 CR_UBA_SEQ : 0 CR_UBA_REC : 0 CR_SFL : 0 CR_CLS_BAS : 0 CR_CLS_WRP : 0 LRBA_SEQ : 1891 LRBA_BNO : 10967 HSCN_BAS : 424789783 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~仅仅记录访问的最高scn号。 HSCN_WRP : 3 HSUB_SCN : 1 US_NXT : 0000000074BDE648 US_PRV : 0000000074BDE648 WA_NXT : 0000000074BDE658 WA_PRV : 0000000074BDE658 OQ_NXT : 000000007D281650 OQ_PRV : 000000007D281650 AQ_NXT : 000000007D281630 AQ_PRV : 000000007D281630 OBJ_FLAG : 242 TCH : 0 TIM : 0 CR_RFCNT : 0 SHR_RFCNT : 0 PL/SQL procedure successfully completed. --//提交呢？ --//session 1: SCOTT@book> commit ; Commit complete. SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424791676 3 3 620 18543 0000000071C3A000 DEPT 0000000084D25320 4 135 1 data block cr 1 424789783 3 3 620 18543 0000000077012000 DEPT 0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT 0000000084D25320 4 135 1 data block xcur 0 0 0 0 0 0 00000000748DE000 DEPT --//注：在提交前我在session 3多执行1次查询，确定state=1的HSCN_BAS是否记录查询时最高scn。 --//session 3, 再次查询呢？ SCOTT@book> select CURRENT_SCN,dept.* from dept,v$database where dept.rowid='AAAVRCAAEAAAACHAAA'; CURRENT_SCN DEPTNO DNAME LOC ------------ ------------ -------------- ------------- 13309693848 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ bh 4 135 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- -------------------- 0000000084D25320 4 135 1 data block cr 1 424791676 3 3 620 18543 0000000071C3A000 DEPT 0000000084D25320 4 135 1 data block cr 1 424789783 3 3 620 18543 0000000077012000 DEPT 0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT 0000000084D25320 4 135 1 data block xcur 1 0 0 0 0 0 00000000748DE000 DEPT --//注意看访问的是stare=XCUR的块，TCH从0->1. SYS@book> SELECT HSCN_BAS,HSCN_WRP FROM x$bh WHERE dbarfil=4 AND dbablk = 135 and state=1; HSCN_BAS HSCN_WRP ------------ ------------ 424791721 3 --//可以确定这个scn就是提交时的scn号。 SYS@book> alter system checkpoint; System altered. --//通过bbed观察,可以确定。 BBED> p /d kcbh dba 4,135 struct kcbh, 20 bytes @0 ub1 type_kcbh @0 6 ub1 frmt_kcbh @1 162 ub1 spare1_kcbh @2 0 ub1 spare2_kcbh @3 0 ub4 rdba_kcbh @4 16777351 ub4 bas_kcbh @8 424791721 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ub2 wrp_kcbh @12 3 ub1 seq_kcbh @14 1 ub1 flg_kcbh @15 6 (KCBHFDLC, KCBHFCKV) ub2 chkval_kcbh @16 37837 ub2 spare3_kcbh @18 0 --// 16777351 = set dba 4,135 = alter system dump datefile 4 block 135 = 0x1000087 总结： 1.测试有点乱，不过如果按照测试做，许多概念还是可以理解清楚的。 2.希望这个测试对与大家理解oracle的基本概念有用。 3.昨晚查了一些资料，发现：http://www.dbi-services.com/index.php/blog/entry/rac-buffer-states-xcur-scur-pi-ci Here are the states we have seen here: XCUR: current version of the block - holding an exclusive lock for it SCUR: current version of the block that can be share because no modification were done CR: only valid for consistent read, after applying the necessary undo to get it back to requried SCN PI: past image of a modified current block, kept until the latest version is checkpointed and the other possible states: FREE: The buffer is not currently in use. READ: when the block is being read from disk MREC: when the block is being recovered for media recovery IREC: when the block is being recovered for crash recovery

编辑推荐：

返回列表

[20201231]单实例data buffer states.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推