[20121021]探究表的rowdependencies屬性.txt
[20121021]探究表的rowdependencies屬性.txt
使用bbed來探索表的rowdependencies屬性.
實際上預設這個功能是不開啟的,也就是採用norowdependencies方式，這樣block的所有行會共享同一個SCN，如果執行DML操作,
這個SCN都是一樣的在同一塊中.而使用rowdependencies.每行都會有對應事務的scn.使用ora_rowscn可以查詢到塊中記錄的SCN。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t ( id number) tablespace test noparallel rowdependencies ;
insert into t values (1);
commit;
insert into t values (2);
commit;
select id,ora_rowscn from t;
SQL> select rowid ,id,ora_rowscn from t;
ROWID ID ORA_ROWSCN
------------------ ---------- ----------
AAAcIPAAIAAAACPAAA 1 3010474766
AAAcIPAAIAAAACPAAB 2 3010474768
--可以發現每行對應不同的scn在同一塊中.
SQL> @ 10to16 3010474766
10 to 16 HEX
--------------
b370330e
SQL> @ 10to16 3010474768
10 to 16 HEX
--------------
b3703310
SQL> @ lookup_rowid AAAcIPAAIAAAACPAAA
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
115215 8 143 0
alter system checkpoint;
1.探究對用的塊:
BBED> set dba 8,143
DBA 0x0200008f (33554575 8,143)
BBED> map /v
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 143 Dba:0x0200008f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdbh, 14 bytes @100
ub1 kdbhflag @100
sb1 kdbhntab @101
sb2 kdbhnrow @102
sb2 kdbhfrre @104
sb2 kdbhfsbo @106
sb2 kdbhfseo @108
sb2 kdbhavsp @110
sb2 kdbhtosp @112
struct kdbt[1], 4 bytes @114
sb2 kdbtoffs @114
sb2 kdbtnrow @116
sb2 kdbr[2] @118
ub1 freespace[8042] @122
ub1 rowdata[24] @8164
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[12]
-----------
ub1 rowdata[12] @8176 0x2c
BBED> x /rnx
rowdata[12] @8176
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x01
cols@8178: 1
col 0[2] @8185: 1
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0] @8164 0x2c
BBED> x /rnx
rowdata[0] @8164
----------
flag@8164: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8165: 0x02
cols@8166: 1
col 0[2] @8173: 2
--奇怪從哪裡獲得scn資訊呢?
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0003
ub2 kxidslt @46 0x0002
ub4 kxidsqn @48 0x000023a5
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c0190b
ub2 kubaseq @56 0x125a
ub1 kubarec @58 0x09
ub2 ktbitflg @60 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xb370330e
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0009
ub2 kxidslt @70 0x0007
ub4 kxidsqn @72 0x0000247c
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c0234b
ub2 kubaseq @80 0x1317
ub1 kubarec @82 0x07
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0xb3703310
--可以發現在itl槽中:
-- ub4 ktbitbas @64 0xb370330e
-- ub4 ktbitbas @88 0xb3703310
-- 與查詢ora_rowscn的數值對應.
SQL> @ 10to16 3010474766
10 to 16 HEX
--------------
b370330e
SQL> @ 10to16 3010474768
10 to 16 HEX
--------------
b3703310
2.難道記錄在itl不成,這樣不是很消耗空間嗎?
繼續插入看看:
SQL> insert into t values (3);
1 row created.
SQL> select current_scn,sysdate from v$database;
CURRENT_SCN SYSDATE
----------- -------------------
3010475483 2012-10-21 09:08:34
SQL> insert into t values (4);
1 row created.
SQL> commit ;
Commit complete.
SQL> select rowid ,id,ora_rowscn from t;
ROWID ID ORA_ROWSCN
------------------ ---------- ----------
AAAcIPAAIAAAACPAAA 1 3010474766
AAAcIPAAIAAAACPAAB 2 3010474768
AAAcIPAAIAAAACPAAC 3 3010475486
AAAcIPAAIAAAACPAAD 4 3010475486
--插入3,4是同一個事務,ORA_ROWSCN相同,應該是commit寫入為準.3010475486>3010475483.
alter system checkpoint;
--再次說明,使用bbed檢視,一定要保證執行alter system checkpoint;,不然看到的資訊可能不正確.
--而且如果使用bbed看到不對,執行alter system checkpoint;後要退出bbed,再進入才行.
BBED> map /v
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 143 Dba:0x0200008f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdbh, 14 bytes @100
ub1 kdbhflag @100
sb1 kdbhntab @101
sb2 kdbhnrow @102
sb2 kdbhfrre @104
sb2 kdbhfsbo @106
sb2 kdbhfseo @108
sb2 kdbhavsp @110
sb2 kdbhtosp @112
struct kdbt[1], 4 bytes @114
sb2 kdbtoffs @114
sb2 kdbtnrow @116
sb2 kdbr[4] @118
ub1 freespace[8014] @126
ub1 rowdata[48] @8140
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[36]
-----------
ub1 rowdata[36] @8176 0x2c
BBED> x /rnx
rowdata[36] @8176
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x00
cols@8178: 1
col 0[2] @8185: 1
BBED> p *kdbr[1]
rowdata[24]
-----------
ub1 rowdata[24] @8164 0x2c
BBED> x /rnx
rowdata[24] @8164
-----------
flag@8164: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8165: 0x00
cols@8166: 1
col 0[2] @8173: 2
BBED> p *kdbr[2]
rowdata[12]
-----------
ub1 rowdata[12] @8152 0x2c
BBED> x /rnx
rowdata[12] @8152
-----------
flag@8152: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8153: 0x01
cols@8154: 1
col 0[2] @8161: 3
BBED> p *kdbr[3]
rowdata[0]
----------
ub1 rowdata[0] @8140 0x2c
BBED> x /rnx
rowdata[0] @8140
----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x01
cols@8142: 1
col 0[2] @8149: 4
--插入3,4的lock:0x01.而先前插入的1,2的lock:0x00
--看看ITL槽:
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0002
ub2 kxidslt @46 0x0013
ub4 kxidsqn @48 0x00002345
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c00989
ub2 kubaseq @56 0x12b8
ub1 kubarec @58 0x10
ub2 ktbitflg @60 0x2002 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xb37035de
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0009
ub2 kxidslt @70 0x0007
ub4 kxidsqn @72 0x0000247c
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c0234b
ub2 kubaseq @80 0x1317
ub1 kubarec @82 0x07
ub2 ktbitflg @84 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0xb3703310
SQL> @ 10to16 3010475486
10 to 16 HEX
--------------
b37035de
--這個可以從這裡查詢:
ub4 ktbitbas @64 0xb37035de
--這樣前面插入1,2的ora_rowscn記錄跑到哪裡去了呢?
BBED> p kdbr
sb2 kdbr[0] @118 8076
sb2 kdbr[1] @120 8064
sb2 kdbr[2] @122 8052
sb2 kdbr[3] @124 8040
BBED> p *kdbr[3]
rowdata[0]
----------
ub1 rowdata[0] @8140 0x2c
BBED> dump /v count 60
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 143 Offsets: 8140 to 8191 Dba:0x0200008f
---------------------------------------------------------------------------------------------------------------------------------------------------------------
2c010100 00000000 0002c105 2c010100 00000000 0002c104 2c000100 00103370 b302c103 2c000100 000e3370 b302c102 l ,...........,...........,.....3p....,.....3p....
0106de35 l ...5
<48 bytes per line>
--為了好看,寫出10與16進位制的對應關係:
3010474766=> b370330e
3010474768=> b3703310
3010475486=> b37035de
2c010100 00000000 0002c105 2c010100 00000000 0002c104 2c000100 00103370 b302c103 2c000100 000e3370 b302c102 l ,...........,...........,.....3p....,.....3p....
~~~~~~ ~~ ~~~~~~~~~
--如果把下劃線的位元組對調(intel系列的小頭在前),完全與3010474766=> b370330e,3010474768=> b3703310相對應,也就是ora_rowscn儲存在行記錄中的.
3.做一個dump看看,也許能說明問題:
SQL> alter system dump datafile 8 block 143;
Block header dump: 0x0200008f
Object id on Block? Y
seg/obj: 0x1c20f csc: 0x00.b37035d2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000088 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.013.00002345 0x00c00989.12b8.10 --U- 2 fsc 0x0000.b37035de
0x02 0x0009.007.0000247c 0x00c0234b.1317.07 C--- 0 scn 0x0000.b3703310
bdba: 0x0200008f
data_block_dump,data header at 0x2a97325264
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x2a97325264
76543210
flag=--R-----
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f68
avsp=0x1f42
tosp=0x1f42
0xe:pti[0] nrow=4 ffs=0
0x12:pri[0] ffs=0x1f8c
0x14:pri[1] ffs=0x1f80
0x16:pri[2] ffs=0x1f74
0x18:pri[3] ffs=0x1f68
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x0 cc: 1
dscn 0x0000.b370330e
col 0: [ 2] c1 02
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x0 cc: 1
dscn 0x0000.b3703310
col 0: [ 2] c1 03
tab 0, row 2, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000
col 0: [ 2] c1 04
tab 0, row 3, @0x1f68
tl: 12 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000
col 0: [ 2] c1 05
end_of_block_dump
End dump data blocks tsn: 9 file#: 8 minblk 143 maxblk 143
--可以發現dscn對應的就是scn.最後插入的記錄3,4,scn資訊(目前是 0x0000.00000000)還儲存在ITL中,如果還有DML操作,會出現在行記錄中.實際上開啟
--rowdependencies,以消耗空間(每條記錄6個位元組)來儲存事務的scn的.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-746863/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 表的itl 屬性
- 遊標屬性介紹.txt
- PostgreSQL：表的儲存屬性SQL
- 表屬性設定
- css屬性與js中style物件的屬性對應表CSSJS物件
- win10系統txt檔案怎麼改屬性_win10 txt檔案如何更改屬性Win10
- form表單的enctype屬性的作用ORM
- 【譯】表單元件的屬性相容性表元件
- 表單元素的form屬性介紹ORM
- Hive學習之更改表的屬性Hive
- HTML5的新的表單屬性HTML
- 管理表空間（表空間的屬性）轉貼
- 營銷屬性表和fm
- 表單元素的form屬性用法介紹ORM
- name屬性是表單元素必須的
- ms sql 獲取表欄位的屬性SQL
- 共有的表單欄位屬性
- [20131212]12c新特性建表 屬性DEFAULT ON NULL.txtNull
- [20190527]注意表與索引的並行屬性.txt索引並行
- html5之新增表單屬性HTML
- 產品集型別和屬性表型別
- 表的storage (MINEXTENTS 屬性對truncate後表大小的影響
- Oracle11gr2新增表的RESULT CACHE屬性Oracle
- [20210903]探究mutex的值.txtMutex
- iOS動畫 屬性屬性解析iOS動畫
- Python 類的屬性與例項屬性Python
- [20190917]oracle引數deferred屬性.txtOracle
- 求助：在html中。option中有個屬性txt有什麼作用？HTML
- css的屬性CSS
- jQuery利用name屬性獲取表單元素jQuery
- 商品 分類 屬性 表設計 庫存
- HTML5表單新增元素與屬性HTML
- 自定義html標籤和表單屬性HTML
- 如何建立能儲存具有不同屬性的產品的表？
- 表單元素同時寫id和name屬性的作用
- C#+arcengine獲得柵格資料的屬性表C#
- CSS 屬性篇(七)：Display屬性CSS
- js如何獲取給定屬性的屬性值JS