分析索引文件中rowid以及伪列rowid
SELECT?T1.owner,T1.object_name,T1.object_idFROM?DBA_OBJECTS T1WHERE?T1.object_name =?'PK_EMP'AND?T1.owner =?'SCOTT'AND?T1.object_type =?'INDEX';?OWNER???? OBJECT_NAME???? OBJECT_ID
SCOTT ? ? ?PK_EMP ? ? ? ? ? ? ? ? ?73197?通过alter session set events ?'immediate trace name ?treedump level?index_object_id' ? --通过查询去获取index_object_id的值?ALTER?SESSION?SET?EVENTS?'IMMEDIATE TRACE NAME TREEDUMP LEVEL 73197';通过上面的events可以获取叶子节点的文件号跟块号Trace file c:\app\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_11516.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU???????????????? : 4 - type 8664, 2 Physical Cores
Process Affinity??? : 0x0x0000000000000000
Memory (Avail/Total): Ph:3835M/7915M, Ph+PgF:9762M/15830M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 22
Windows thread id: 11516, image: ORACLE.EXE (SHAD)
*** 2013-06-20 21:56:44.071
*** SESSION ID:(130.16) 2013-06-20 21:56:44.071
*** CLIENT ID:() 2013-06-20 21:56:44.071
*** SERVICE NAME:(orcl) 2013-06-20 21:56:44.071
*** MODULE NAME:(PL/SQL Developer) 2013-06-20 21:56:44.071
*** ACTION NAME:(SQL Window - New) 2013-06-20 21:56:44.071
----- begin tree dump
leaf:?0x100009b 16777371?(0: nrow: 14 rrow: 14) ? ? ? ? ? ? ? ?----数值是一样的，只是一个是2进制一个是16进制
----- end tree dump?select?to_number('100009b'?,'xxxxxxx')??from?dual;TO_NUMBER('100009B','XXXXXXX')
16777371?select?dbms_utility.data_block_address_file(16777371?) "文件号",dbms_utility.data_block_address_block(?16777371) "块号"from?dual;文件号???? 块号
4 ? ? ? ? ? ? 155?alter?system?dump?datafile?4?block?155;Trace file c:\app\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_11516.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU???????????????? : 4 - type 8664, 2 Physical Cores
Process Affinity??? : 0x0x0000000000000000
Memory (Avail/Total): Ph:3835M/7915M, Ph+PgF:9762M/15830M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 22
Windows thread id: 11516, image: ORACLE.EXE (SHAD)
*** 2013-06-20 21:56:44.071
*** SESSION ID:(130.16) 2013-06-20 21:56:44.071
*** CLIENT ID:() 2013-06-20 21:56:44.071
*** SERVICE NAME:(orcl) 2013-06-20 21:56:44.071
*** MODULE NAME:(PL/SQL Developer) 2013-06-20 21:56:44.071
*** ACTION NAME:(SQL Window - New) 2013-06-20 21:56:44.071
----- begin tree dump
leaf: 0x100009b 16777371 (0: nrow: 14 rrow: 14)
----- end tree dump
Start dump data blocks tsn: 4 file#:4 minblk 155 maxblk 155
*** 2013-06-20 22:10:09.380
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777371
BH (0x000007FF9EF6C9C8) file#: 4 rdba: 0x0100009b (4/155) class: 1 ba: 0x000007FF9E0B4000
? set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 46,28
? dbwrid: 0 obj: 73197 objn: 73197 tsn: 4 afn: 4 hint: f
? hash: [0x000007FFC1ABC028,0x000007FFC1ABC028] lru: [0x000007FF9EF6CD10,0x000007FF9EF6C980]
? ckptq: [NULL] fileq: [NULL] objq: [0x000007FFBA1FA9C8,0x000007FFBA1FA9C8]
? st: XCURRENT md: NULL tch: 6
? flags:
? LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
? cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100009b (4/155)
scn: 0x0000.000e6080 seq: 0x01 flg: 0x06 tail: 0x60800601
frmt: 0x02 chkval: 0xec89 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001F118200 to 0x000000001F11A200
01F118200 0000A206 0100009B 000E6080 06010000? [.........`......]
01F118210 0000EC89 00000002 00011DED 000E6077? [............w`..]
01F118220 00000000 00320002 01000098 00000000? [......2.........]
01F118230 00000000 00000000 00000000 00000000? [................]
01F118240 00000000 001F0009 000002E0 00C001EC? [................]
01F118250 0049007E 0000200E 000E6080 00000000? [~.I.. ...`......]
01F118260 00000000 01800000 00000000 0040000E? [..............@.]
01F118270 1E7D1EBD 00000000 00000000 00000000? [..}.............]
01F118280 00000006 00001F64 1F4C1F58 1F341F40? [....d...X.L.@.4.]
01F118290 1F1C1F28 1F041F10 1EEC1EF8 1ED51EE0? [(...............]
01F1182A0 1EBD1EC9 00000000 00000000 00000000? [................]
01F1182B0 00000000 00000000 00000000 00000000? [................]
??????? Repeat 486 times
01F11A120 01020000 00970000 50C2030D 01020023? [...........P#...]
01F11A130 00970000 50C2030C 01020003 00970000? [.......P........]
01F11A140 50C2020B 00010200 0A009700 4D4FC203? [...P..........OM]
01F11A150 00010200 09009700 2D4FC203 00010200? [..........O-....]
01F11A160 08009700 284FC203 00010200 07009700? [......O(........]
01F11A170 594EC203 00010200 06009700 534EC203? [..NY..........NS]
01F11A180 00010200 05009700 634DC203 00010200? [..........Mc....]
01F11A190 04009700 374DC203 00010200 03009700? [......M7........]
01F11A1A0 434CC203 00010200 02009700 164CC203? [..LC..........L.]
01F11A1B0 00010200 01009700 644BC203 00010200? [..........Kd....]
01F11A1C0 00009700 464AC203 00000000 00000000? [......JF........]
01F11A1D0 00000000 00000000 00000000 00000000? [................]
??????? Repeat 1 times
01F11A1F0 00000000 00000000 00000000 60800601? [...............`]
Block header dump:? 0x0100009b
Object id on Block? Y
seg/obj: 0x11ded? csc: 0x00.e6077? itc: 2? flg: E? typ: 2 - INDEX
???? brn: 0? bdba: 0x1000098 ver: 0x01 opc: 0
???? inc: 0? exflg: 0
Itl?????????? Xid????????????????? Uba???????? Flag? Lck??????? Scn/Fsc
0x01?? 0x0000.000.00000000? 0x00000000.0000.00? ----??? 0? fsc 0x0000.00000000
0x02?? 0x0009.01f.000002e0? 0x00c001ec.007e.49? --U-?? 14? fsc 0x0000.000e6080
Leaf block dump
===============
header address 521241188=0x1f118264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 14
kdxcofbo 64=0x40
kdxcofeo 7869=0x1ebd
kdxcoavs 7805
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8036
row#0[8024] flag: ------, lock: 2, len=12, data:(6):??01 00 00 97 00 00
col 0; len 3; (3):? c2 4a 46
row#1[8012] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 01
col 0; len 3; (3):? c2 4b 64
row#2[8000] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 02
col 0; len 3; (3):? c2 4c 16
row#3[7988] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 03
col 0; len 3; (3):? c2 4c 43
row#4[7976] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 04
col 0; len 3; (3):? c2 4d 37
row#5[7964] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 05
col 0; len 3; (3):? c2 4d 63
row#6[7952] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 06
col 0; len 3; (3):? c2 4e 53
row#7[7940] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 07
col 0; len 3; (3):? c2 4e 59
row#8[7928] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 08
col 0; len 3; (3):? c2 4f 28
row#9[7916] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 09
col 0; len 3; (3):? c2 4f 2d
row#10[7904] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 0a
col 0; len 3; (3):? c2 4f 4d
row#11[7893] flag: ------, lock: 2, len=11, data:(6):? 01 00 00 97 00 0b
col 0; len 2; (2):? c2 50
row#12[7881] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 0c
col 0; len 3; (3):? c2 50 03
row#13[7869] flag: ------, lock: 2, len=12, data:(6):? 01 00 00 97 00 0d
col 0; len 3; (3):? c2 50 23
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 155 maxblk 155?01 00 00 97 00 00 ? 16进制----》2进制 ?00000001 00000000 00000000 10010111 00000000 00000000?转换为2进制后前十位表示文件号 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??00000001 00? ? ? ? ? ---------------------->4?中间22位表示块号 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??000000 00000000 10010111 ----------->151最后16位表示行号 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??00000000 00000000上面分析的是index数据文件中存储的rowid，这是没有记录数据对象编号的rowid?下面看伪列rowid数据对象编号??????? 文件编号??????? 块编号?????????? 行编号
OOOOOO???????????? FFF ? ? ? ? ? ?BBBBBB ? ? ? ? ? ?RRRAAAR3s ? ? ? ? ? ? ? ? ? ?AAE ? ? ? ? ? ? ? ?AAAACX ? ? ? ? ? ? ?AAA ? --64进制A-Z ?0-25a-z ?26-510-9 ?52-61+/ ? 62-63?select?t.rowid,dbms_rowid.rowid_object(t.rowid) object_id,dbms_rowid.rowid_relative_fno(t.rowid) file_id,?????? dbms_rowid.rowid_block_number(t.rowid) block_id,dbms_rowid.rowid_row_number(t.rowid) row_idfrom?scott.emp t;?ROWID ? ? ? ? OBJECT_ID???? FILE_ID???? BLOCK_ID???? ROW_ID
AAAR3sAAEAAAACXAAA???? 73196???? 4???? 151???? 0
AAAR3sAAEAAAACXAAB???? 73196???? 4???? 151???? 1
AAAR3sAAEAAAACXAAC???? 73196???? 4???? 151???? 2
AAAR3sAAEAAAACXAAD???? 73196???? 4???? 151???? 3
AAAR3sAAEAAAACXAAE???? 73196???? 4???? 151???? 4
AAAR3sAAEAAAACXAAF???? 73196???? 4???? 151???? 5
AAAR3sAAEAAAACXAAG???? 73196???? 4???? 151???? 6
AAAR3sAAEAAAACXAAH???? 73196???? 4???? 151???? 7
AAAR3sAAEAAAACXAAI???? 73196???? 4???? 151???? 8
AAAR3sAAEAAAACXAAJ???? 73196???? 4???? 151???? 9
AAAR3sAAEAAAACXAAK???? 73196???? 4???? 151???? 10
AAAR3sAAEAAAACXAAL???? 73196???? 4???? 151???? 11
AAAR3sAAEAAAACXAAM???? 73196???? 4???? 151???? 12
AAAR3sAAEAAAACXAAN???? 73196???? 4???? 151???? 13