[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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-746863/