oracle實驗記錄 （block cleanout）
主要看下塊清除
undo blk 未commit前 已經寫回 datafile， 此時commit,延遲塊清除產生datablk 不會被從datafile讀入buffer cache，undo blk呢
SQL> select * from test;
A
----------
9
9
SQL> select * from test;
A
----------
9
9
SQL> select file#,block# from (select dbms_rowid.rowid_relative_fno(rowid) file#
,dbms_rowid.rowid_block_number(rowid) block# from test);
FILE# BLOCK#
---------- ----------
6 50
6 50
SQL> update test set a=9;
2 rows updated.
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK
---------- ---------- ---------- ---------- ----------
4 25 407 2 1630
SQL> select name from v$rollname where usn=4;
NAME
------------------------------
_SYSSMU4$
SQL> alter system dump datafile 6 block 50;
System altered.
SQL> alter system dump undo header '_SYSSMU4$';
System altered.
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00277541 seq: 0x01 flg: 0x04 tail: 0x75410601
frmt: 0x02 chkval: 0x2ff4 type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.23b1db itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e ---- 2 fsc 0x0000.00000000
0x02 0x0009.022.000001b7 0x0080008c.0250.63 C--- 0 scn 0x0000.0023916f
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 0a
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 0a
end_of_block_dump
SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('80065e','xx
xxxxx'));
PL/SQL procedure successfully completed.
SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('80065e','xx
xxxxx'));
PL/SQL procedure successfully completed.
SQL> print file#
FILE#
----------
2
SQL> print blk#
BLK#
----------
1630
SQL> select file_id,block_id from dba_extents where segment_name='_SYSSMU4$';
FILE_ID BLOCK_ID
---------- ----------
2 57
2 17
2 1545
SQL> select file#,block#,class#,status from v$bh where file# in(6,2) and block#
in(1630,57,17,1545,50);
FILE# BLOCK# CLASS# STATUS
---------- ---------- ---------- -------
2 57 23 xcur
6 50 1 cr
6 50 1 xcur
2 1630 24 xcur
SQL> alter system flush buffer_cache;
System altered.
SQL> select file#,block#,class#,status ,dirty from v$bh where file# in(6,2) and
block# in(1630,57,17,1545,50);
FILE# BLOCK# CLASS# STATUS D
---------- ---------- ---------- ------- -
2 57 23 free N
6 50 1 free N
6 50 1 free N
2 1630 24 free N
SQL> commit;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~執行updata 的 session commit
Commit complete.
QL> /
FILE# BLOCK# CLASS# STATUS D
--------- ---------- ---------- ------- -
2 57 23 free N
2 57 23 xcur Y~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~讀入 undo header了 修改了 undo header tran tab
6 50 1 free N
6 50 1 free N
2 1630 24 free N
SQL> select * from test;~~~~~~~~~~~~~~~~~~~~另一個SESSION 查詢，產生delayed block cleanout
A
----------
9
9
SQL> /
FILE# BLOCK# CLASS# STATUS D
---------- ---------- ---------- ------- -
2 57 23 free N
2 57 23 xcur Y~~~~~~~~~~~~~~~~~
6 50 1 free N
6 50 1 free N
6 50 1 xcur Y~~~~~~~~~~~~~~~~~~~~~~~~讀入data block 產生delayed block cleanout
2 1630 24 free N
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select * from test;
A
----------
9
9
SQL> update test set a=6;
2 rows updated.
SQL> alter system dump datafile 6 block 50;
System altered.
Dump file e:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_16484.trc
Mon May 04 18:09:40 2009
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity: 0x00000000
Memory (A/P) : PH:787M/2047M, PG:2749M/3945M, VA:1716M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Windows thread id: 16484, image: ORACLE.EXE (SHAD)
*** SERVICE NAME:(SYS$USERS) 2009-05-04 18:09:40.265
*** SESSION ID:(136.3790) 2009-05-04 18:09:40.265
Start dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00280758 seq: 0x01 flg: 0x00 tail: 0x07580601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.2777c5 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 ---- 2 fsc 0x0000.00000000
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
*** 2009-05-04 18:10:12.171
SQL>
SQL> commit;
SQL> alter system dump datafile 6 block 50;
System altered.
Start dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00280775 seq: 0x01 flg: 0x02 tail: 0x07750601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.2777c5 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 --U- 2 fsc 0x0000.00280775
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 6 block 50;
Start dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00280775 seq: 0x01 flg: 0x06 tail: 0x07750601
frmt: 0x02 chkval: 0x26ec type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.2777c5 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 --U- 2 fsc 0x0000.00280775
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
System altered.
SQL> select * from test;
A
----------
6
6
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 --U- 2 fsc 0x0000.00280775
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607615/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 大資料實驗記錄大資料
- mysql load 相關實驗記錄MySql
- Oracle Block Cleanouts 塊清除OracleBloC
- 【BLOCK】Oracle 塊管理常用SQLBloCOracleSQL
- STM32F207DAC實驗記錄
- SEO 經驗記錄
- overlay網路隔離實驗失敗記錄
- 《learn to count everything》論文閱讀、實驗記錄
- Oracle實驗(04)：floatOracle
- block實現原理BloC
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- AutoreleasePool、Block、Runloop整理筆記BloCOOP筆記
- iOS Block學習筆記iOSBloC筆記
- STM32F207串列埠實驗記錄串列埠
- 使用 Block 實現 KVOBloC
- 理解 Block 實現原理BloC
- Oracle實驗(01)：字元 & 位元組Oracle字元
- Oracle實驗(03)：number的使用Oracle
- Oracle 11g DG新特性--Automatic block repairOracleBloCAI
- oracle awr快照點不記錄問題Oracle
- 關於 block 會不會被自動 copy 的實驗和猜想BloC
- 專案重構經驗記錄
- Laravel 使用個人經驗記錄Laravel
- Oracle實驗(02)：轉換 & 轉譯Oracle
- Oracle實驗(05)：時間型別Oracle型別
- 【Oracle 恢復表空間】 實驗Oracle
- 實驗 20：備忘錄模式模式
- Oracle遊標遍歷%rowtype中的記錄Oracle
- Oracle 41億資料量表建立索引記錄Oracle索引
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- Objective-C記憶體管理:BlockObject記憶體BloC
- Unity Application Block 1.2 學習筆記UnityAPPBloC筆記
- 很漂亮的Python驗證碼（記錄）Python
- Laravel unique驗證 排除當前記錄Laravel
- oracle 主外來鍵關係及實驗Oracle
- Oracle實驗8--Merge與歸檔Oracle
- 使用ogg 從oracle 同步mysql遇到問題記錄OracleMySql
- 11.21實驗 20：備忘錄模式模式
- iOS - 對 block 實現的探究iOSBloC