分析索引文件中rowid以及伪列rowid

最新推荐文章于 2024-10-30 11:02:01 发布

iteye_3560

最新推荐文章于 2024-10-30 11:02:01 发布

阅读量153

点赞数

文章标签：数据库嵌入式

SELECT T1.owner,T1.object_name,T1.object_id

FROM DBA_OBJECTS T1

WHERE 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可以获取叶子节点的文件号跟块号

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 RRR

AAAR3s AAE AAAACX AAA --64进制

A-Z 0-25

a-z 26-51

0-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_id

from 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