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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 塊延遲清除(delayed block cleanout) 理解OracleBloC
- oracle block cleanout塊清除_延遲塊清除OracleBloC
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- oracle實驗記錄 (oracle reset parameter)Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （dump logfile）Oracle
- oracle實驗記錄 （事務控制）Oracle
- oracle實驗記錄 （函式index）Oracle函式Index
- oracle實驗記錄 （bigfile tablespace）Oracle
- oracle實驗記錄 （恢復-redo）Oracle
- oracle實驗記錄 （expdp/impdp使用）Oracle
- oracle實驗記錄 （transport tablespace(Rman)）Oracle
- oracle實驗記錄 （使用exp/imp）Oracle
- oracle實驗記錄 （dump index b*tree）OracleIndex
- oracle實驗記錄 （CKPT的觸發）Oracle
- oracle實驗記錄 （dump undo4）Oracle
- oracle實驗記錄 （dump undo3）Oracle
- oracle實驗記錄 （dump undo2）Oracle
- oracle實驗記錄 手工 duplicate database(1)OracleDatabase
- oracle實驗記錄Rman duplicate database(1)OracleDatabase
- oracle實驗記錄 （許可權,role）Oracle
- oracle實驗記錄 （SQL*PLUS 命令操作）OracleSQL
- oracle實驗記錄 （PFILE 啟動SPFILE）Oracle
- oracle實驗記錄 （SHARED server MODE）OracleServer
- oracle實驗記錄Rman duplicate database 2OracleDatabase
- oracle實驗記錄（恢復-checkpoint cnt）Oracle
- oracle實驗記錄 （可恢復session）OracleSession
- oracle實驗記錄 （transport tablespace(EXPDP/IMPDP)）Oracle
- oracle實驗記錄 （使用outlines）Oracle
- oracle實驗記錄 （管理outlines）Oracle