[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，建立新的資料快取。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2747410/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201231]單例項data buffer states.txt單例
- Oracle Data BufferOracle
- 單例項刪除ASM例項單例ASM
- 單例與單例項之爭單例
- websocket簡單例項Web單例
- RAC 例項 遷移到 單例項 -- 使用RMAN Duplicate單例
- 11GR2 Active Duplicate過程(單例項對單例項)單例
- JavaScript單例模式概念與例項JavaScript單例模式
- 單例模式 - 只有一個例項單例模式
- PHP 完整表單例項PHP單例
- 5.6單例項安裝單例
- RAC起單例項make單例
- oracle單例項轉RACOracle單例
- Redis單例項安裝Redis單例
- data buffer cache的一點總結。
- tf.data.Dataset.shuffle(buffer_size)中buffer_size的理解
- js選項卡簡單程式碼例項JS
- opengl簡單入門例項
- RAC+DG(asm單例項)ASM單例
- rac恢復到單例項單例
- Vue單檔案模板例項Vue
- javascript事件冒泡簡單例項JavaScript事件單例
- Spark 簡單例項（基本操作）Spark單例
- mysql 5.7單例項安裝MySql單例
- javascript this用法和簡單例項JavaScript單例
- 搭建RAC到單例項DG單例
- 單例項恢復至RAC單例
- zabbix監控單例項redis單例Redis
- 單例項的duplicate(non ASM)單例ASM
- 從Data Buffer中讀資料是單塊讀，還是多塊讀？
- 使用點陣圖選單項——點陣圖選單項例項 (轉)
- jQuery data-*賦值或者獲取值程式碼例項jQuery賦值
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項OracleLinux
- 單例項和單點故障怎麼辦？單例
- data buffer cache的一點總結 -- 轉
- canal同步mysql，監聽單例項，多例項配置MySql單例
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項-3OracleLinux
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項 -2OracleLinux