查询出表的rowid信息:
SQL> select t.*,t.rowid from lock_test t;
ID NAME ROWID
---------- -------------------- ------------------
1 ran AAE5IpAABAAAoqiAAA
2 yuan AAE5IpAABAAAoqiAAB
3 ran3 AAE5IpAABAAAoqiAAC
3 4 AAE5IpAABAAAoqiAAD
3 4 AAE5IpAABAAAoqiAAE
分析第一行的rowid
AAE5IpAABAAAoqiAAA
AAE5Ip:数据对象编号 object_id
AAB:相对文件编号
AAA:数据块编号
oqiAAA:数据行号
rowid每个字符对应的数字
A~Z 0~25
a~z 26~51
0~9 52~61
+ 62
/ 63
由于rowid显示的是用64进制所以需要做如下转换(手工转换)
select 4*64*64*64+57*64*64+8*64+41 from dual; 1282601
select 1 from dual; 1
select 40*64*64+42*64+34 from dual; 166562
select 0 from dual; 0
也可以使用包来转换
SQL> select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id ,dbms_rowid.rowid_row_number(rowid) num from lock_test;
OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
1282601 1 166562 0
1282601 1 166562 1
1282601 1 166562 2
1282601 1 166562 3
1282601 1 166562 4
也可以根据自定义函数来转换
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
/
函数创建好之后就可以使用了:
SQL> select get_rowid('AAE5IpAABAAAoqiAAA') row_id from dual;
ROW_ID
-------------------------------------------------------------
Object# is :1282601
Relative_fno is :1
Block number is :166562
Row number is :0
刚才提到的是rowid的显示方式:基于64位编码的18个字符显示,其实rowid的存储方式是:10 个字节即80位存储,其中数据对象编号需要32 位,相关文件编号需要10 位,块编号需要22,位行编号需要16 位,由此,我们可以得出:
32bit的object number,每个数据库最多有4G个对象
10bit的file number,每个对象最多有1022个文件(2个文件预留)
22bit的block number,每个文件最多有4M个BLOCK
16bit的row number,每个BLOCK最多有64K个ROWS
rowid分析完了 开始dump数据块了
alter system dump datafile 1 block 166562
第一部分:
Start dump data blocks tsn: 0 file#: 1 minblk 166562 maxblk 166562
buffer tsn: 0 rdba: 0x00428aa2 (1/166562)
scn: 0x0000.157bd0e5 seq: 0x01 flg: 0x06 tail: 0xd0e50601
frmt: 0x02 chkval: 0xe11f type: 0x06=trans data
第二部分:
Block header dump: 0x00428aa2
Object id on Block? Y
seg/obj: 0x139229 csc: 0x00.15755708 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.018.0003192e 0x008001b9.3edb.2e --U- 1 fsc 0x0000.157bc975
0x02 0x0005.01a.00014798 0x00800cfa.2131.06 --U- 1 fsc 0x0000.157bd0e5
第三部分:
data_block_dump,data header at 0x1609f65c
===============
tsiz: 0x1fa0
hsiz: 0x1c
pbl: 0x1609f65c
bdba: 0x00428aa2
76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f47
avsp=0x1f52
tosp=0x1f52
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0x1f57
0x14:pri[1] offs=0x1f61
0x16:pri[2] offs=0x1f6c
0x18:pri[3] offs=0x1f4f
0x1a:pri[4] offs=0x1f47
block_row_dump:
tab 0, row 0, @0x1f57
tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 72 61 6e
tab 0, row 1, @0x1f61
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 4] 79 75 61 6e
tab 0, row 2, @0x1f6c
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 4] 72 61 6e 33
tab 0, row 3, @0x1f4f
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 34
tab 0, row 4, @0x1f47
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 34
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 166562 maxblk 166562