[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單例
- [20210104]禁用或者刪除觸發器2.txt觸發器
- [20201231]RAC buffer states: XCUR, SCUR, PI,CR.txt
- [20191204]關於oracle例項是否使用hugepages問題2.txtOracle
- PHP 完整表單例項PHP單例
- Redis單例項安裝Redis單例
- tf.data.Dataset.shuffle(buffer_size)中buffer_size的理解
- rac恢復到單例項單例
- 單例項mysql.yaml kubernetes單例MySqlYAML
- opengl簡單入門例項
- Spark 簡單例項（基本操作）Spark單例
- RAC+DG(asm單例項)ASM單例
- canal同步mysql，監聽單例項，多例項配置MySql單例
- 利用白名單繞過360例項
- EventBus詳解及簡單例項單例
- 例項QT程式 —— Qt單例不規則介面程式QT單例
- C++學習隨筆——簡單的單例設計模式例項C++單例設計模式
- RAC+單例項DG的切換單例
- python 單一程式例項 實現Python
- JavaScript 表單驗證程式碼例項JavaScript
- 快遞鳥查詢訂單例項單例
- oracle之 單例項監聽修改埠Oracle單例
- docker 執行elasticsearch單例項（elasticsearch:7.12.0）DockerElasticsearch單例
- Java的Socket通訊簡單例項Java單例
- ElasticSearch客戶端簡單操作例項Elasticsearch客戶端
- 從例項出發，瞭解單例模式和靜態塊單例模式
- Spring 原始碼學習 - 單例bean的例項化過程Spring原始碼單例Bean
- 超級簡單入門vuex 小例項Vue
- 淡入淡出效果簡單程式碼例項
- XML節點自動生成簡單例項XML單例
- C#out引數的簡單例項C#單例
- Flutter中表單元件綜合運用例項Flutter元件
- [20221111]19c配置Data Guard Broker問題2.txt
- php例項化物件的例項方法PHP物件
- phpqrcode生成動態二維碼簡單例項PHP單例
- webpack+react+antd單頁面應用例項WebReact
- JavaScript正規表示式備忘單附例項JavaScript
- Python簡單函式迴圈綜合例項Python函式