dump index的同时对rowid存储的解析
转自 http://blog.csdn.net/stevendbaguo/article/details/8215225
本地有个pub_department的表，索引为PK_PUB_DEPARTMENT。
1.找到object_id
select object_id from dba_objects s where s.object_name='PK_PUB_DEPARTMENT';
OBJECT_ID
----------
64408
2.进行转储
alter session set events 'immediate trace name treedump level 64408';
----- begin tree dump
branch: 0x201262433629732 (0: nrow:2,level:1)
leaf: 0x2012626 33629734 (-1: nrow: 292 rrow:292)
leaf: 0x2012627 33629735 (0: nrow: 311 rrow:311)
----- end tree dump
select dbms_utility.data_block_address_file(33629735) "file",
dbms_utility.data_block_address_block(33629735) "block"
from dual;
file block
---------- ----------
8 75303
alter system dump datafile 8 block 75303;
。。。。。。。。。。。。省略部分内容。。。。。。。。。。。。
row#310[7921] flag:------, lock: 0, len=11, data:(6): 02 06 d6 25 00 0d
col 0; len 2; (2): c35b
可以看到索引的内部结构为值(c35b为900000)+rowid(0206d625000d此处只是文件号+块号+行数)
select utl_raw.cast_to_number('c35b') from dual;
UTL_RAW.CAST_TO_NUMBER('C35B')
------------------------------
900000
3.对rowid的解析
select rowid,dbms_rowid.rowid_object(rowid) object_id,--(AAAMim)AAFAAAAAMAAC数据对象号
dbms_rowid.rowid_relative_fno(rowid) file_id,-- AAAMim(AAF)AAAAAMAAC 相对文件号
dbms_rowid.rowid_block_number(rowid) block_id,-- AAAMimAAF(AAAAAM)AAC 在第几个块
dbms_rowid.rowid_row_number(rowid) num -- AAAMimAAFAAAAAM(AAC)在block中的行数
from pub_departmentwhere department_id =900000;
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------------------ ---------- ---------- ---------- ----------
AAAPuXAAIAABtYlAAN 64407 8 448037 13
select rowid,dump(rowid,16) dump_rowid from pub_department where department_id=900000;
ROWID DUMP_ROWID
-------------------- --------------------------------------------------
AAAPuXAAIAABtYlAAN Typ=69 Len=10:0,0,fb,97,2,6,d6,25,0,d
SELECT pkg_number_trans.f_hex_to_dec('fb97') FROM dual;--64407 ojbect_id
--第5、6为的值除以64得到的商是相对文件号，余数是BLOCK号的高位，
乘以65536后加上低两位才是BLOCK号。
包pkg_number_trans来自于http://blog.csdn.net/guogang83/article/details/8002014
SELECT pkg_number_trans.f_hex_to_dec('0206') FROM dual;
select 518/64 from dual; --8 文件号
select mod(518,64) from dual- -6余数为block的高位
SELECT pkg_number_trans.f_hex_to_dec('06d625') FROM dual;--448037 块号
SELECT pkg_number_trans.f_hex_to_dec('0d') FROM dual;--13 行号
